Date Time queries

[grant]

I have an MS Access database that stores dates/Times in the following
format:
2008/10/10 09:32:20 AM.

I create the following query:
select userid, chectime from checkinout where chectime = #10/10/2008#

I have loads of records from that date but none get returned! The last row
of the table has the latest date and time and that is the only row that gets
returned. Why is it doing this and how would I write a query that selects a
specific date between certain times?

Thanks,
Grant

 

[Stefan Hoffmann]

hi Grant,

I have an MS Access database that stores dates/Times in the following
format:
2008/10/10 09:32:20 AM.

I create the following query:
select userid, chectime from checkinout where chectime = #10/10/2008#

Date/Time values are not splittable, so your short #10/10/2008# is
expanded by Jet as #10/10/2008 00:00:00#. This cannot return a match in
your case.

So you need to do some date/time math in your conditional:

SELECT userid, chectime
FROM checkinout
WHERE CDate(CLng(chectime)) = #10/10/2008#

The time part of a date/time value is stored as the fractional part, so
using the CLng() function cuts it off.

mfG
--> stefan <--

 

[Douglas J. Steele]

hi Grant,

Date/Time values are not splittable, so your short #10/10/2008# is
expanded by Jet as #10/10/2008 00:00:00#. This cannot return a match in
your case.

So you need to do some date/time math in your conditional:

SELECT userid, chectime
FROM checkinout
WHERE CDate(CLng(chectime)) = #10/10/2008#

The time part of a date/time value is stored as the fractional part, so
using the CLng() function cuts it off.

Or (possibly a little more efficient, since it doesn't require the function
calls for each row in the table)

SELECT userid, chectime
FROM checkinout
WHERE chectime BETWEEN #2008-10-10# AND #2008-10-11#

 

[grant]

Thanks for your help gents that answered my question.