Re: Running a query to identify clashes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <Hicksy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BEFAFA4F-6EA1-4D25-A268-E500FDF417F4@xxxxxxxxxxxxxxxx
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" <Hicksy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:23A8D3D1-BC65-431D-9D51-A388BADD6700@xxxxxxxxxxxxxxxx
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" <Hicksy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EA011393-443A-4F58-BB0E-228BC89DF35C@xxxxxxxxxxxxxxxx
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" <Hicksy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:90326A82-6234-4DBF-8925-C4CA108CE931@xxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: Running a query to identify clashes
    ... Whenever i make a booking which clashes it accepts it regardless! ... Private Sub Form_BeforeUpdate ... Dim strWhere As String ... that query checks every record in the table against every other record to ...
    (microsoft.public.access.queries)
  • Re: Checking for results of SQL statements without saving queries.
    ... Use DLookup() to see if there is a clash. ... Private Sub Form_BeforeUpdate ... Dim strWhere As String ... > don't know how to check if a SELECT query doesn't give any result using ...
    (microsoft.public.access.queries)
  • Re: Table Validation
    ... This query does indeed do what I want it to do. ... Private Sub Form_BeforeUpdate ... Dim strWhere As String ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • cmd button to open a form with a query
    ... You don't really need the query, ... Use it to set the Filter property of the form. ... Private Sub MyButton_Click ... Dim strWhere as String ...
    (microsoft.public.access.forms)
  • Checking for Overlapping Events in 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. ... Dim strWhere As String ... Running a query to identify clashes ... identifies any clashes for bookings of vehicles to eliminate double bookings. ...
    (microsoft.public.access.queries)