date format mismatch

[sunshine123]

Hi,

I'm using access and my date field is formatted as shortdate - dd/mm/yyyy.
But when I try to query the table as below, there is a datatype mismatch.

SELECT *
FROM tablename
WHERE datefield='12/04/2007';

is it possible to format the date field in the sql statement or should I format the date value in the front end of the application and then pass this.

I would appreciate any help. Thanks in advance

[Hack]

That is because Access does not support single quotes for dates.

In Access, you need to enclose dates with the # sign.

[si_the_geek]

For Access, Dates need to be surrounded by # instead of '

Much more importantly, dates in an SQL string will always be interpreted as MM/DD/YYYY unless they are blatantly meant to be different (such as 31/04/2007).

When putting dates into SQL strings you should format them to either MM/DD/YYYY or (better) YYYY/MM/DD

[sunshine123]

Well I tried using hash as below

SELECT *
FROM tablename
WHERE datefield=#12/11/2007#;

but no data was returned in Access although the table had data for 12/11/2007. in the table though the value is displayed in dd/mm/yyyy format when i click on that field it becomes 12/11/2007 7:58:10 AM.

I'm passing this as a paramter from my front end in C#, since it kept throwing a datattype mismatch error 'm trying to make it work inside access.

[si_the_geek]

It doesn't matter what the date format is in the table design (that is only used for Access forms etc), in SQL statements you must use the formats I suggested.

[sunshine123]

Hmmm...ok.....but why isn't the query returning any data despite having matches in the table?? I'm using the format u suggested.

[si_the_geek]

In that case I would assume that there is also a time in the field. If that is the case, the field will not be exactly the value you specified.

One way to solve it is to use Between with the time for the end of the day, eg:

WHERE date1 Between #04/13/2007# AND #04/13/2007 23:59:59#