Date format in SQL

[Guest]

I want to create a from which takes the user input date from a text box,
assigns it to a variable (using VBA) and then uses that variable to from part
of my WHERE clause in an SQL query (not QBE Grid query) and then execute that
SQL query.

I have done similar things with text before but not dates.

The date format on the form I want to use is DD/MM/YYYY.
I believe that SQL queries in MS Access are formatted in MM/DD/YYYY.

I was wondering if you know if I will experience any trouble with the date
format, and if you have any tips for this (or the process as a whole).

An example query at the moment using just hard dates, is as follows.
lic_grant (is licence granted date)
lic_sur (is licence surrendered date)


SELECT tblCustomer.company, tblCustomer.lic_sur, tblCustomer.lic_grant,
FROM tblCustomer
WHERE (((tblCustomer.lic_sur) Is Null) AND
((tblCustomer.lic_grant)<=#6/30/2005#)) OR
(((tblCustomer.lic_sur)>=#7/1/2004#) AND
((tblCustomer.lic_grant)<=#6/30/2005#))
ORDER BY tblCustomer.lic_grant;

Your assistance is appreciated.

 

[Marshall Barton]

I want to create a from which takes the user input date from a text box,
assigns it to a variable (using VBA) and then uses that variable to from part
of my WHERE clause in an SQL query (not QBE Grid query) and then execute that
SQL query.

I have done similar things with text before but not dates.

The date format on the form I want to use is DD/MM/YYYY.
I believe that SQL queries in MS Access are formatted in MM/DD/YYYY.

I was wondering if you know if I will experience any trouble with the date
format, and if you have any tips for this (or the process as a whole).

An example query at the moment using just hard dates, is as follows.
lic_grant (is licence granted date)
lic_sur (is licence surrendered date)


SELECT tblCustomer.company, tblCustomer.lic_sur, tblCustomer.lic_grant,
FROM tblCustomer
WHERE (((tblCustomer.lic_sur) Is Null) AND
((tblCustomer.lic_grant)<=#6/30/2005#)) OR
(((tblCustomer.lic_sur)>=#7/1/2004#) AND
((tblCustomer.lic_grant)<=#6/30/2005#))
ORDER BY tblCustomer.lic_grant;

You're right, date literals must be in an unambiguous format
(e.g. # yyyy-mm-dd#) or in USA format #m/d/yyyy#

The trick is to convert your date value variable to a
literal in the SQL string. Because of differing Windows
regional settings, you need to use the Format function to do
it:

tblCustomer.lic_sur >= Format(dtSur, "\#m\/d\/yyyy\#")

The reason for the \ before the / is that an unescaped /
will be replaced by the regional setting for the date
separator character, which might not be acceptable in USA
format.

 

[JohnFol]

Marshal, just remember #m/d/yyyy# is ambiguous for all non-us countries as
it's not clear if 01/02/2005 is Feb or March.

 

[Marshall Barton]

Of course it is! That's why I used "unambiguous format"
and "USA format" in two separate phrases.

Regardless, whatever you, I, or anyone else thinks is the
"natural" way to express a date, the only thing that counts
is how Windows/Access interprets it. Fortunately, or
unfortunately, depending on your point of view at the time,
Access tries very hard (sometimes I think too hard) to make
sense of all the potential ways of expressing a date so,
since there are almost an unlimited number of ways of
interpreting a string of characters as a date, some rules
must be applied. Even you and I would likely agree that 12
May 2005 is an unambiguous date, but to non-English versions
of Windows/Access, that would be incomprehensible.