Date format in SQL

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.

--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

Dylan Moran wrote:

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.

--
Marsh
MVP [MS Access]

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" wrote in message
...
Dylan Moran wrote:

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.

--
Marsh
MVP [MS Access]

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.
--
Marsh
MVP [MS Access]



JohnFol wrote:
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.


Dylan Moran wrote:
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;


"Marshall Barton" wrote
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.