what is the syntax for query date/time in ms access...
"select date from table1 where pDate = #10/03/2005#"
it return me empty record. pls help

Is pdate defined as a "DATE" type in the table. Access will not return a good value if the table type is not "DATE" for pDate. It let's you use the "#" for dates, but doesn't return anything for a value.

Let me know if this was the issue.

RU1

RU1 has a point. Out of curiousity, is the name of your field and table really DATE and TABLE1? If so, change DATE to something else. Date() is an Access function and, therefore, reserved.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

My actual attribute name is SNo, and table name is Staff.
thanks for your reply. i hav solve it by

SELECT SNo
FROM Staff
WHERE Format([ComplaintDate],"yyyy-mm-dd")>='2005-03-09'

Thank you very much!!!! :)

What you discovered is that a date time field in a table includes the time. So you weren't getting an exact match when you used the equal operator because of the time in the field.

In your last example you coule have done:

Where ComplaintDate >= #03/09/2005#

It's not likely that you'd ever get exactly equal to 03/09/2005.

BTW, if you wanted just 3/9 you'd code:

Where (ComplaintDate >= #03/09/2005#) and (ComplaintDate < #03/10/2005#)

Your Format function makes it easier to point to a single date.


Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org