dates in access

[neeeel]

I am running a query in vba where i want to select records by date

I have checked that the relevant date is in the table, but the query is not finding it

Set rec = dbs.OpenRecordset("SELECT * from E0 WHERE E0.Date = #" & "09/05/2010" & "#")

There is definitely a record with the date 09/05/2010, but this query doesnt find it. But if i change the date to 05/05/2010 then it finds the record with 05/05/2010 as date fine. I am really really confused as to whats going on here, any ideas?

[hansup]

Your SELECT statement will retrieve the record from 9/5/2010 only if its time component is midnight. Check what time is included with the date value.

Create a new query in Access and paste this into the SQL View:SELECT Format([Date], "yyyy/mm/dd hh:mm:ss") as formatted_date
FROM EO
WHERE [Date] >= #2010/09/05# And [Date] < #2010/09/06#;

Date is a reserved word in Access. Since you're using it as a field name, I enclosed it in square brackets to make clear to Access you're not asking for the VBA Date() function.