Regardless of what your regional settings are, you have to use mm/dd/yyyy
format in SQL statements. (Okay, that statement isn't 100% accurate: you can
use any unambiguous format. The point is, you can't reliably use dd/mm/yyyy
format, since it will only be interpretted correctly for days of 13 or
higher)
See
http://www.mvps.org/access/datetime/date0005.htm at "The Access Web" for
one approach.
Another approach is to use the CDate function, which does respect regional
settings. The "trick" is that you need to enclose the date in quotes for
this to work. If you've got 10/11/2003 in a string strDate and you want that
to be recognized as 10 Nov, 2003 on a machine where the regional settings
have the short date format as dd/mm/yyyy, you can use "... WHERE
MyTable.MyDate = CDate('" & strDate & "')"
To make it more obvious, that's
"... WHERE MyTable.MyDate = CDate( ' " & strDate & " ' ) "
You may find Allen Browne's
http://members.iinet.net.au/~allenbrowne/ser-36.html useful reading.
--
Doug Steele, Microsoft Access MVP
(No private e-mails, please)
Mr. x said:
Thanks,
... But I don't want to use the parameters.
I want a solution that is something I will change in the where clause.
Something like : ... while my_date_col = #dd/mm/yyyy# (in the dd I put the
day etc... but this doens't work, because computer ask me to use the format
of mm/dd/yyyy).
(What I need is a conversion function to date + sample, that can be used in
an sql query, please).
Thanks
Try
PARAMETERS [Enter Date:] DateTime;
Select my_date_col
from my_Table
where my_date_col = [Enter Date:]
--
HTH
Van T. Dinh
MVP (Access)
Mr. x said:
Hello,
In Access database, I do the follow :
Select my_date_col
from my_Table
where my_date_col = #13/11/2003 // the 13th of november 2003.
The problem is that I want to ask in the where clause, in the format of
"dd/mm/yyyy".
I want to force the specific format in my query (so it won't be something
else, like mm/yy/dddd format).
How can I do that ?
Thanks
