R: I need to create a date/time search query but i cant figure it out

[Craig Armitage]

Hi Wayne,

Thanks thats sorted part of the problem, it now finds the date correctly..

Im doing this in VBCode so I was wondering if you could help with the other
part of the problem... The times..

I assumed from the previous example you gave that the following would work
but sadly it doesnt..

SELECT * FROM Appointments WHERE AppDate=#17/04/07# AND ((AppStartTime <=
#09:00:00#) AND (AppEndTime => #10:00:00#))

The idea is to return records that return records that fall over a selected
time.

Hope you can see what im doing wrong!

 

[Ken Sheridan]

There are a couple of things wrong with your query:

1. Date literals must be in US format or an otherwise internationally
unambiguous format. With 17 April it will nevertheless work because there is
no month 17 so Access knows to transpose the day and month. A date like 4
July, however would be interpreted as 7 April.

2. The => operator should be >=.

So the query should read:

SELECT *
FROM Appointments
WHERE AppDate = #04/17/2007#
AND AppStartTime <= #09:00:00#
AND AppEndTime >= #10:00:00#;

This will return all rows where an appointment starts at or before 9.00 AM
and ends at or after 10.00 AM on 17 April.

Ken Sheridan
Stafford, England