Checking for Overlapping Events in access 2007
in [Queries]
|
Prev: Multiple Query Report- Really Need HELP!
Next: Passing info into a text field when doing the update query
From: leo on 13 Jan 2010 20:53 Hi, Please help, as soon i wont have much hair left!!! I have constructed a db, that has vehicles, customers, order_details and driver tables. I need when someone adds a new order to a form "order Details" that when they select "save" or close the current form event it searches in the relevent tables to check that the Vehicle and driver are not already booked. is not being used already. My feilds on orders details form are "Driver_name" "Time_of_collection" "time of delivery" "date_of_booking" "Vehicle ID". Date format is DD/MM/YYYY (short date) Time format is HH:MM I am using Access 2007. I have managed to write some code, but it saves it as normal but does not check if the vehicle or driver is in use for that date and time slot. Can you Help??? Private Sub Form_AfterUpdate() Dim strWhere As String Dim varResult As Variant If ((Vehicle_ID = Me.Vehicle_ID) _ And (Me.Date_of_Booking = Me.Date_of_Booking.OldValue) _ And (Me.Time_of_Collection = Me.Time_of_Collection.OldValue) _ And (Me.Time_Of_delivery = Me.Time_Of_delivery.OldValue)) _ Or IsNull(Me.Vehicle_ID) _ Or IsNull(Me.Date_of_Booking) _ Or IsNull(Me.Time_of_Collection) _ Or IsNull(Me.Time_Of_delivery) Then Else strWhere = "(Vehicle_ID) = Me.Vehicle_ID & " And "(Date_of_booking" < " & (Me.date_of_booking) & " _ And (" &(Me.Time_of_Collection) & " < Time_of_Collection) varResult = DLookup(Vehicle_ID, "Vehicles", strWhere) If Not IsNull(varResult) Then Cancel = True MsgBox "Clash with booking " & varResult & "." End If End If End Sub Douglas J. Steele wrote: You've got a lot of conditions in that If statement. 18-Nov-07 You've got a lot of conditions in that If statement. Are they all on the same line (and it's wrapped in your post), or are they really on separate lines? They need to be all on one line, or else you need to use continuation characters: If ((Me.VehicleID = Me.VehicleID.OldValue) _ And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue) _ And(Me.BookingReturnDateAndTime = _ Me.BookingReturnDateAndTime.OldValue)) _ Or IsNull(Me.VehicleID) _ Or IsNull (Me.BookingOutDateAndTime) _ Or IsNull (Me.BookingReturnDateAndTime)Then As well, regardless of your regional settings, you cannot use dd/mm/yyyy in SQL statements (and even if you could, your strcJetDate is still incorrect). Change it to Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#" or Const strcJetDate = "\#yyyy\-mm\-dd hh\:mm\:ss\#" If you were hoping to only compare down to the minute, ignoring seconds, you can't do it in that way. You'd have to use strWhere = "(VehicleID = " & Me.VehicleID & ")AND (Format(BookingOutDateAndTime, "yyyymmddhhnn") < " & Format(Me.BookingReturnDateAndTime, "yyyymmddhhnn") & ") AND (" & Format(Me.BookingOutDateAndTime, "yyyymmddhhnn") & " < Format(BookingOutDateAndTime, "yyyymmddhhnn") AND (BookingID <> " & Me.BookingID & ")" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Hicksy" <Hicksy(a)discussions.microsoft.com> wrote in message news:23A8D3D1-BC65-431D-9D51-A388BADD6700(a)microsoft.com... Previous Posts In This Thread: On Friday, November 16, 2007 8:38 AM Hicks wrote: Running a query to identify clashes Hi I am creating a Vehicle Bookings database and have created a query which identifies any clashes for bookings of vehicles to eliminate double bookings. When a user books a car, i would like the database to check with the query to see if the vehicle is available and if it is, accept the booking. If it is already booked, i would like a message to appear telling the user to try another time or another car. COuld someone please help me with this please? I have nearly finished it and this is one of the few final problems i have! Many thanks On Friday, November 16, 2007 9:22 AM Allen Browne wrote: Use the BeforeUpate event procedure of the Form where the entry is made. Use the BeforeUpate event procedure of the Form where the entry is made. Use DLookup() to see if there is a clashing entry. For help with DLookup(), see: http://allenbrowne.com/casu-07.html The basic idea is that there is a clash if: - this booking starts before the other one ends, and - the other one starts before this one ends, and - it's the same vehicle, and - it's not clashing with itself. So it will be something like this aircode: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Const strcJetDate = "\#mm\/dd\/yyyy\#" If ((Me.VehicleID = Me.VehicleID.OldValue) _ And (Me.StartDate = Me.StartDate.OldValue) _ And (Me.EndDate = Me.EndDate.OldValue)) _ Or IsNull(Me.VehicleID) _ Or IsNull(Me.StartDate) _ Or IsNull(Me.EndDate) Then 'do nothing Else strWhere = "(VehicleID = " & Me.VehicleID & _ ") AND (StartDate < " & Format(Me.EndDate, strcJetDate) & _ ") AND (" & Format(Me.StartDate, strcJetDate) & _ " < EndDate) AND (ID <> " & Me.ID & ")" varResult = DLookup("ID", "BookingTable", strWhere) If Not IsNull(varResult) Then Cancel = True MsgBox "Clash with booking " & varResult & "." End If End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Hicksy" <Hicksy(a)discussions.microsoft.com> wrote in message news:90326A82-6234-4DBF-8925-C4CA108CE931(a)microsoft.com... On Friday, November 16, 2007 10:26 AM Hicks wrote: Many thanks Allen.It was your query I used to establish clashing appointments! Many thanks Allen. It was your query I used to establish clashing appointments! Do i need the query at all then? thanks "Allen Browne" wrote: On Friday, November 16, 2007 11:02 AM Allen Browne wrote: Re: Running a query to identify clashes If you are referrring to the query in: Clashing Events/Appointments at: http://allenbrowne.com/appevent.html that query checks every record in the table against every other record to identify any clashes. If you are only interested in the record that's about to be saved, you only need to compare that one against all the others, so the Cartesian query is not needed. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Hicksy" <Hicksy(a)discussions.microsoft.com> wrote in message news:EA011393-443A-4F58-BB0E-228BC89DF35C(a)microsoft.com... On Sunday, November 18, 2007 9:11 AM Hicks wrote: Thanks Allen. Thanks Allen. I have tried the code but i am getting several errors such as "syntax error", "Expected: line number 0r label or statement or end of statement". I have copied my code below: Any more help would be really appreciated! Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Const strcJetDate = "\#dd\/mm\/yyyy\/hh\/mm\#" If ((Me.VehicleID = Me.VehicleID.OldValue) And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue) And(Me.BookingReturnDateAndTime = Me.BookingReturnDateAndTime.OldValue)) Or IsNull(Me.VehicleID) Or IsNull (Me.BookingOutDateAndTime) Or IsNull (Me.BookingReturnDateAndTime)Then 'do nothing Else strWhere = "(VehicleID = " & Me.VehicleID & ")AND (BookingOutDateAndTime < " & Format(Me.BookingReturnDateAndTime, strcJetDate) & ") AND (" & Format(Me.BookingOutDateAndTime, strcJetDate) & " < BookingOutDateAndTime) AND (BookingID <> " & Me.BookingID & ")" varResult = DLookup("BookingID", "tblBookings", strWhere) If Not IsNull(varResult) Then Cancel = True MsgBox "Clash with booking " & varResult & "." End If End If End Sub "Allen Browne" wrote: On Sunday, November 18, 2007 10:59 AM Douglas J. Steele wrote: You've got a lot of conditions in that If statement. You've got a lot of conditions in that If statement. Are they all on the same line (and it's wrapped in your post), or are they really on separate lines? They need to be all on one line, or else you need to use continuation characters: If ((Me.VehicleID = Me.VehicleID.OldValue) _ And(Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue) _ And(Me.BookingReturnDateAndTime = _ Me.BookingReturnDateAndTime.OldValue)) _ Or IsNull(Me.VehicleID) _ Or IsNull (Me.BookingOutDateAndTime) _ Or IsNull (Me.BookingReturnDateAndTime)Then As well, regardless of your regional settings, you cannot use dd/mm/yyyy in SQL statements (and even if you could, your strcJetDate is still incorrect). Change it to Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#" or Const strcJetDate = "\#yyyy\-mm\-dd hh\:mm\:ss\#" If you were hoping to only compare down to the minute, ignoring seconds, you can't do it in that way. You'd have to use strWhere = "(VehicleID = " & Me.VehicleID & ")AND (Format(BookingOutDateAndTime, "yyyymmddhhnn") < " & Format(Me.BookingReturnDateAndTime, "yyyymmddhhnn") & ") AND (" & Format(Me.BookingOutDateAndTime, "yyyymmddhhnn") & " < Format(BookingOutDateAndTime, "yyyymmddhhnn") AND (BookingID <> " & Me.BookingID & ")" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Hicksy" <Hicksy(a)discussions.microsoft.com> wrote in message news:23A8D3D1-BC65-431D-9D51-A388BADD6700(a)microsoft.com... On Monday, November 19, 2007 6:06 AM Hicks wrote: Thanks for your help. Thanks for your help. Whenever i make a booking which clashes it accepts it regardless! My code is below: Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strWhere As String Dim varResult As Variant Const strcJetDate = "\#mm\/dd\/yyyy hh\:mm\:ss\#" If ((Me.VehicleID = Me.VehicleID.OldValue) And (Me.BookingOutDateAndTime = Me.BookingOutDateAndTime.OldValue) And (Me.BookingReturnDateAndTime = Me.BookingReturnDateAndTime.OldValue)) Or IsNull(Me.VehicleID) Or IsNull(Me.BookingOutDateAndTime) Or IsNull(Me.BookingReturnDateAndTime) Then 'do nothing Else strWhere = "(VehicleID = " & Me.VehicleID & ")AND (BookingOutDateAndTime < " & Format(Me.BookingReturnDateAndTime, strcJetDate) & ") AND (" & Format(Me.BookingOutDateAndTime, strcJetDate) & " < BookingOutDateAndTime) AND (BookingID <> " & Me.BookingID & ")" varResult = DLookup("BookingID", "tblBookings", strWhere) If Not IsNull(varResult) Then Cancel = True MsgBox "Clash with booking " & varResult & "." End If End If End Sub "Douglas J. Steele" wrote: On Monday, November 19, 2007 5:42 PM Douglas J. Steele wrote: What's actually being written to strWhere? Is it correct? What's actually being written to strWhere? Is it correct? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) On Tuesday, November 27, 2007 7:22 AM Hicks wrote: To be honest, I really don't know what is going on! To be honest, I really don't know what is going on! Can you please help me get this right as i is one of the few things i need to do to complete the database. All i want to do is check that there are no bookings already made for the vehicle the user wishes to book. many thanks "Douglas J. Steele" wrote: On Tuesday, November 27, 2007 10:02 AM Michel Walsh wrote: It seems there is an error in the formulation. It seems there is an error in the formulation. The check on a colliding interval must be like: AnyExistingStart < NewProposedEnding AND AnyExistingEnd > NewProposedStarting the one you use is BookingOut < NewProposedReturning AND NewProposedStarting < BookingOut ie, you use BookingOut twice ! Vanderghast, Access MVP "Hicksy" <Hicksy(a)discussions.microsoft.com> wrote in message news:BEFAFA4F-6EA1-4D25-A268-E500FDF417F4(a)microsoft.com... On Wednesday, January 13, 2010 8:51 PM leo cleverdon wrote: Checking for Overlapping Events in access 2007 Hi, Please help, as soon i wont have much hair left!!! I have constructed a db, that has vehicles, customers, order_details and driver tables. I need when someone adds a new order to a form "order Details" that when they select "save" or close the current form event it searches in the relevent tables to check that the Vehicle and driver are not already booked. is not being used already. My feilds on orders details form are "Driver_name" "Time_of_collection" "time of delivery" "date_of_booking" "Vehicle ID". Date format is DD/MM/YYYY (short date) Time format is HH:MM I am using Access 2007. I have managed to write some code, but it saves it as normal but does not check if the vehicle or driver is in use for that date and time slot. Can you Help??? Private Sub Form_AfterUpdate() Dim strWhere As String Dim varResult As Variant If ((Vehicle_ID = Me.Vehicle_ID) _ And (Me.Date_of_Booking = Me.Date_of_Booking.OldValue) _ And (Me.Time_of_Collection = Me.Time_of_Collection.OldValue) _ And (Me.Time_Of_delivery = Me.Time_Of_delivery.OldValue)) _ Or IsNull(Me.Vehicle_ID) _ Or IsNull(Me.Date_of_Booking) _ Or IsNull(Me.Time_of_Collection) _ Or IsNull(Me.Time_Of_delivery) Then Else strWhere = "(Vehicle_ID) = Me.Vehicle_ID & " And "(Date_of_booking" < " & (Me.date_of_booking) & " _ And (" &(Me.Time_of_Collection) & " < Time_of_Collection) varResult = DLookup(Vehicle_ID, "Vehicles", strWhere) If Not IsNull(varResult) Then Cancel = True MsgBox "Clash with booking " & varResult & "." End If End If End Sub Submitted via EggHeadCafe - Software Developer Portal of Choice File Management For No-Touch Deployments http://www.eggheadcafe.com/tutorials/aspnet/d69b8cb9-00ef-4f71-93ef-ec39c7640d46/file-management-for-noto.aspx |