If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Running a query to identify clashes
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" wrote in message ... 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 |
#3
|
|||
|
|||
Running a query to identify clashes
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: 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" wrote in message ... 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 |
#4
|
|||
|
|||
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" wrote in message ... 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: 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" wrote in message ... 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 |
#5
|
|||
|
|||
Running a query to identify clashes
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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
#6
|
|||
|
|||
Running a query to identify clashes
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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
#7
|
|||
|
|||
Running a query to identify clashes
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: 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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
#8
|
|||
|
|||
Running a query to identify clashes
What's actually being written to strWhere? Is it correct?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Hicksy" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
#9
|
|||
|
|||
Running a query to identify clashes
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: What's actually being written to strWhere? Is it correct? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Hicksy" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
#10
|
|||
|
|||
Running a query to identify clashes
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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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: 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" wrote in message ... 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 |
|
Thread Tools | |
Display Modes | |
|
|