ADO + MS Access Cannot use "date" as query condition

First I have to apologize about posting the problem here , since it not actually relate to SQL Server

I am really new to database programming.

I use ADO (the ActiveX one , Not ADO.NET) In an MFC Application to access MS ACCESS Database

I can insert date in to table using statement like this

insert into tableXXX (date_field) values ('1/2/2007')

but when i tried to query it using this statement

select * from tableXXX where date_field = #1/2/2007#

Nothing return

I also tried to use #1-2-2007# , #01-02-2007# but it all the same

Can someone tell me how to successfuly use date as a query condition ?

Thank in Advance

As far as I can remember from my access days you can also use the ISO format to query for dates in access:

= 20070201

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

It look like I have to subtract year with 543 (I use thai regional and standard/format setting),

But I still dont under stand why.

Since when I view it in Microsoft Access the date is correct as I inserted it (2007)

When I convert date return by "select" statement using COLEDateTime,It also autometicaly

subtract the year part with 543 (but nothing is done when using american standard/format)

Can somebody explain this to me?