Re: Running a query to identify clashes

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
    ... All i want to do is check that there are no bookings already made for the ... Private Sub Form_BeforeUpdate ... Dim strWhere As String ... that query checks every record in the table against every other record ...
    (microsoft.public.access.queries)
  • Re: Designing a Booking System
    ... What you need is a crosstab query. ... DayPart) and then go Quriy> Crosstab to change it to a crosstab query. ... from the bookings table. ... Do Until vdat> dto ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Running a query to identify clashes
    ... that query checks every record in the table against every other record to ... Tips for Access users - http://allenbrowne.com/tips.html ... I am creating a Vehicle Bookings database and have created a query ...
    (microsoft.public.access.queries)
  • Re: Union Query with a Crosstab Query
    ... [Bookings and Pipe by Advertiser - RVP].Rep, ... The above query should show the Goals amount for just one of the records ...
    (microsoft.public.access.queries)
  • Re: Running a query to identify clashes
    ... need to compare that one against all the others, so the Cartesian query is ... Tips for Access users - http://allenbrowne.com/tips.html ... I am creating a Vehicle Bookings database and have created a query ...
    (microsoft.public.access.queries)