HELP!! - Dates in SQL

[Guest]

Hi,

I am writing code to generate dynamic SQL - depending on what the users
picks from combo boxes.

Everything nearly working 100% but Dates are driving me to despair!!

The dates I am using are as follows:

FromDate - 01/01/06 (1st Jan 2006)
ToDate - 31/12/10 (31st Dec 2010)


Access seems to do funny things - code wise it appears correct. when I take
the SQL statement generated and paste it in a new query window, Access seems
to jumbe up the 2nd Date to read #10/12/31#

The line of code that generated the SQL statement is:
varSQL = "SELECT Count(tblEvents.EventStatus) AS CountOfEventStatus FROM
tblEvents WHERE (((tblEvents.EventStatus)=" & Chr(34) & "held" & Chr(34) & ")
AND ((tblEvents.EventDate) Between " & Chr(35) & varFrom & Chr(35) & " and "
& Chr(35) & varTo & Chr(35) & "));"


which generates the following SQL statement:
SELECT Count(tblEvents.EventStatus) AS CountOfEventStatus FROM tblEvents
WHERE (((tblEvents.EventStatus)="held") AND ((tblEvents.EventDate) Between
#01/01/00# and #31/12/10#));


All looks good but the results are wrong. When I switch to Query Design
window the 2nd date appear jumbled and the between statements read:

Between #01/01/00# And #10/12/31#

Clearly an issue with the way Access SQL handles dates but for the life of
me I can't fix it!!

Help!!

 

[Allen Browne]

In the interface, Access uses whatever your regional settings say.
But in a SQL string or in VBA code, it uses the American format.

If it did not work that way, it would be impossible to write any code or SQL
that worked consistently anywhere on the planet.

After working with Access since version 1 in a d/m/y country, my suggestions
are in this article:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Douglas J Steele]

Regardless of what your Short Date format is set to through Regional
Settings, you must use mm/dd/yyyy format in your SQL statements. (OK, this
isn't strictly true: you can use any unambiguous format, such as dd mmm yyyy
or yyyy-mm-dd. The point is, you cannot use dd/mm/yyyy and expect it to
work. Access will always try mm/dd/yyyy first. When it encounters something
that it doesn't recognize, such as 31/12/10, it's going to try a number of
possibilities, and will stop when it encounters the first possibility that's
valid.)

Take a look at Allen Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I have in my September 2003
Access Answers column for Pinnacle Publication's "Smart Access" newsletter.
(The column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[Guest]

Hi Marek,

Replace the "between ... and ......" in SQL statement as under:

The line of code that generated the SQL statement is:

varSQL = "SELECT Count(tblEvents.EventStatus) AS CountOfEventStatus FROM
tblEvents WHERE (((tblEvents.EventStatus)=" & Chr(34) & "held" & Chr(34) & ")
AND ((tblEvents.EventDate) >= " & Chr(35) & varFrom & Chr(35) & " and
(tblEvents.EventDate)<="
& Chr(35) & varTo & Chr(35) & "));"

I guess the problem is with "Between" operator which won't work in VBA SQL.

Goodluck,
Surendran

 

[Douglas J Steele]

There's no such thing as "VBA SQL".

The problem (as Allen & I reported already in this thread) is that Access
doesn't correctly recognize dates formatted as dd/mm/yyyy in SQL. For the
first 12 days of each month, the date will be interpretted as being in
mm/dd/yyyy format.