SQL statement with dates/times

[Rose B]

I am trying to do some checks in a module to highlight overlapping
dates/times. I have a query that has existing bookings, with 'StartTime' and
'EndTime' fields holding dd/mm/yyyy hh:nn data. I am passing to the module
the start and end times (also as full date/time fields) of the event that I
am trying to check - as vDateFrom and vDateTo arguments.

HOWEVER - I have a feeling (through various diagnostic messages) that the
tests are not using the time element of the fields from the query. (I could
be wrong - I know that dealing with dates/times is not as straightforward as
one might hope for!).

My code is .....

Set rst = CurrentDb.OpenRecordset("SELECT * from qryBookings WHERE (((# "
& Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") & "# > StartTime AND # " &
Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") _
& "# < EndTime) OR (# " & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "# >
StartTime AND #" & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "# < EndTime)))
OR (#" & Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") _
& "# < StartTime) AND (# " & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "# >
EndTime) AND ResourceID = " & vResID & " AND BookingRef <> " & vBookRef)


The code runs, but records are selected that I don't think should be - and I
can't work out why!

If anyone can help to correct the above code - or suggest another way of
skinning this cat I will be very grateful.

Thanks in advance

 

[Dirk Goldgar]

I am trying to do some checks in a module to highlight overlapping
dates/times. I have a query that has existing bookings, with 'StartTime'
and
'EndTime' fields holding dd/mm/yyyy hh:nn data.

Well, actually they don't. If they are date/time fields, the dates are
stored in an internal format that is not the same as the formatted
"dd/mm/yyyy hh:nn" strings that you see.

I am passing to the module
the start and end times (also as full date/time fields) of the event that
I
am trying to check - as vDateFrom and vDateTo arguments.

HOWEVER - I have a feeling (through various diagnostic messages) that the
tests are not using the time element of the fields from the query. (I
could
be wrong - I know that dealing with dates/times is not as straightforward
as
one might hope for!).

My code is .....

Set rst = CurrentDb.OpenRecordset("SELECT * from qryBookings WHERE (((# "
& Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") & "# > StartTime AND # " &
Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") _
& "# < EndTime) OR (# " & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "# >
StartTime AND #" & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "# <
EndTime)))
OR (#" & Format(vDateFrom, "yyyy/mm/dd hh:nn:ss") _
& "# < StartTime) AND (# " & Format(vDateTo, "yyyy/mm/dd hh:nn:ss") & "#
EndTime) AND ResourceID = " & vResID & " AND BookingRef <> " & vBookRef)


The code runs, but records are selected that I don't think should be - and
I
can't work out why!

If you have verified that the vDateFrom and vDateTo are in the correct
sequence (that is, vDateFrom precedes vDateTo), then your query criteria can
be simplified. If vDateFrom <= EndTime and vDateTo >= StartTime, then you
have an overlap. So try this:

Set rst = CurrentDb.OpenRecordset( _
"SELECT * from qryBookings WHERE " & _
"ResourceID=" & vResID & " AND " & _
"BookingRef<>" & vBookRef & " AND " & _
Format(vDateFrom, "\#yyyy/mm/dd hh:nn:ss\#") & "<=EndTime AND " & _
Format(vDateTo, "\#yyyy/mm/dd hh:nn:ss\#") & ">=StartTime")

I could, of course, have messed that up, but please try it and see if it
give you the correct results.

 

[Rose B]

Thanks Dirk - you are a saviour!! It was a bit more complex that your
solution, due to the fact that the dates (from/to and start/end) could cover
multiple days, but with the formatting that you provided and with switching
the booking ref and resource tests to the front all is now well. Thank you so
much - I was beginning to tear my hair out!!!!