MS Access SELECT and WHERE

Question

I am having trouble getting records out of a database for a certain date. This this the SQL command I have at the moment:

SELECT * FROM ict1 WHERE date='26/03/1992'

It should return one record from my database. "date" is a column and one of the records has '26/03/1992' as the value. I have tested the db connection and I can use:

SELECT * from ict1

So I know it's not that. It's probably just the SQL syntax being a lot different, I'm used to MySQL :@)

Should probably mention that I'm using .NET with an OleDbConnection.

Thanks.

Answer 1

Use the date as YYYY/MM/DD format:

SELECT * FROM ict1 WHERE date='1992/03/26'

SELECT * FROM ict1 WHERE date=#1992/03/26#

Answer 2

Usually dates need to be formatted as for access like the following

Select * from ict1 where date= #03/26/1992#

The # denotes a date in access. Be very careful with dates formatted as '10/03/1992' as it could mean 10th of march or 3rd of october depending on where you are.

Also #1992/03/26# also works

Answer 3

You may want to use a date comparison function instead of date = #xxxx/xx/xx#. Date comparisons do not yield expected results because of formatting and data type issues. In SQL Server your date might be stored as a datetime or a date data type. You need to make sure you are comparing things in the same type and format.

DateDiff ("d", #1992/03/26#, date) = 0