seeking syntax for date in where clause in Jet OLEDB

Question

Hello, I have a problem of using the where clause for limiting dates

I can't even get a simple statement like on "Feb 5 2010" to work, e.g.,

select * from LineItems where DueDate = 2/5/2010; I tried "2/5/2010" "2010/2/5" "2010-2-5" "2010-02-05" 2010-2-5 2010-02-05 ... but none worked.

Does anyone have an idea what the proper format for the date should be? And should it be quoted?

Thank You!

Answer 1

This works for me in a Query

SELECT *
FROM Table1
where mydate = 2010/2/5

Writing a date like that is unambiguous; the other way round will depend on your locale settings.

Answer 2

Hey Guys, I did figured out that you must surround the date with a pair of #'s. Just leaving the date unsurrounded does not work for me. I figured this out by saving a "Filter" in the Data View of Access as query, then I looked at that query in SQL view.