Definitively, dates don’t work within WHERE clauses

[José António Silva]

At least from Access 2000 there are some problems with WHERE clauses which
contains dates. In Access 2007 these problems remain and are somewhat
extended. Due to this, I just finished to change all my queries with
Field.SomeDate = #31/1/2009# (US Format) to Field.SomeDate = CDate (
“2009-01-31â€) (MyRoundAboutFormat). Uuufffffhhh!!!!!!

I’m going now to precise this problem as far as I can:
1) Problem overall environment
a. Access databases with ODBC linked tables to an SQL Server
database.
b. Compiled databases (either accde or accdr) in machines with
Access 2007 run-time (not full) version.
c. With Vista and Access 2007 full version everything goes fine
even with compiled databases. I didn’t test a XP Machine with full version of
Access 2007 and compiled databases.
d. Due to some hardware issues I also didn’t test so far a
machine with Access run-time and a non compiled accdb file.

2) Access 2003 doesn’t run queries with “US format†when the resulting SQL
expression is assigned to a form recordsource. There is a little exception
here that may help to diagnose this problem. Access 2003 works fine with “US
format†that runs against a table where the first lines of data contain
dates/times with times different from #00:00:00#.

3) Access 2003 runs queries with “US format†if they are used to open a ADO
recordset.

4) Access 2007 is stricter. It doesn´t run queries with “US format†neither
to be assigned to a form recordsource nor to be used in a recordset. It also
doesn’t matter if the targeted table has times different from #00:00:00# in
the first lines or not.

5) However, both Access 2003 and 2007 allow to use US format, for example,
in an UPDATE statement: “UPDATE Table SET Field.SomeDate = #31/1/2009#â€, and
in a similar way in an INSERT statement

6) These problems occur with Windows Portuguese and Access Run-Time
Portuguese versions.

7) But, installing Access Run-Time English version in a Windows Portuguese
version also doesn’t solve any problem. Changing all my Windows Portuguese
Version Regional settings to US either doesn’t solve any problem.

8) I usually get error 2473 or error 0 both without description (i.e. “â€)
I’ve come a long way since I realized this to be most serious problem I
found in Access. And I still think the same.

I need to be free of using US format anywhere! I need to be sure that I can
continue ……. just just coding queries with dates.

This is as serious as basic!

Could anybody at Microsoft look at this, please!

Thank you.

Too see some more about this you could read my post “A date problem with
more than 10 years†in this discussion group: 5/15/2009.

 

[Alex Dybenko]

Hi,
the only correct format jet database is #mm/dd/yyyy#
Just convert all your dates to it using format(somedate,"\#mm\/dd\/yyyy\#")
when you use date in SQL, and problem will be solved
#31/1/2009# is a wrong one

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Alex Dybenko]

Hi,
the only correct format jet database is #mm/dd/yyyy#
Just convert all your dates to it using format(somedate,"\#mm\/dd\/yyyy\#")
when you use date in SQL, and problem will be solved
#31/1/2009# is a wrong one

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John W. Vinson]

I just finished to change all my queries with
Field.SomeDate = #31/1/2009# (US Format)

That's a much too sensible format to be accepted in the US <g>...

The US format has month/day/year so this would be #1/31/2009#.

I'll have to respectfully disagree with Alex that it is the *only* accepted
date format - yyyy.mm.dd works also.

 

[John W. Vinson]

I just finished to change all my queries with
Field.SomeDate = #31/1/2009# (US Format)

That's a much too sensible format to be accepted in the US <g>...

The US format has month/day/year so this would be #1/31/2009#.

I'll have to respectfully disagree with Alex that it is the *only* accepted
date format - yyyy.mm.dd works also.

 

[Jack Leach]

yyyy.mm.dd works also.

I use this format for everything. Especially great for recordkeeping, as
they are sorted chronologically by filenames (somewhere I came up the idea
that this was the informal standard, but I have no idea where it came from or
how valid it is).

I don't recall ever having problems with it in development.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

yyyy.mm.dd works also.

I use this format for everything. Especially great for recordkeeping, as
they are sorted chronologically by filenames (somewhere I came up the idea
that this was the informal standard, but I have no idea where it came from or
how valid it is).

I don't recall ever having problems with it in development.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[John W. Vinson]

I use this format for everything. Especially great for recordkeeping, as
they are sorted chronologically by filenames (somewhere I came up the idea
that this was the informal standard, but I have no idea where it came from or
how valid it is).

http://www.iso.org/iso/support/faqs...used_standards_other/date_and_time_format.htm

 

[John W. Vinson]

I use this format for everything. Especially great for recordkeeping, as
they are sorted chronologically by filenames (somewhere I came up the idea
that this was the informal standard, but I have no idea where it came from or
how valid it is).

http://www.iso.org/iso/support/faqs...used_standards_other/date_and_time_format.htm

 

[José António Silva]

I’m very sorry to have wrong typed the US format.
So, in my post, where is #31/1/2009#, it should be #1/31/2009#. And all the
post is about this later format, which I try to use for ages.
And, by the way, I’ve got the same problems if I try to use the suggested
ISO format “yyyy-mm-ddâ€. I’ve got the error 2473 with no description.
If you both don’t have problems with these formats, I wonder if you are
talking about machines with only Access run-time and compiled databases accde
or accdr.

 

[José António Silva]

I’m very sorry to have wrong typed the US format.
So, in my post, where is #31/1/2009#, it should be #1/31/2009#. And all the
post is about this later format, which I try to use for ages.
And, by the way, I’ve got the same problems if I try to use the suggested
ISO format “yyyy-mm-ddâ€. I’ve got the error 2473 with no description.
If you both don’t have problems with these formats, I wonder if you are
talking about machines with only Access run-time and compiled databases accde
or accdr.

 

[NevilleT]

I have had similar issues. Usually resort to one of two solutions. Either
put the date in a date variable before using it, or use DateSerial.

If I had a field on a form (Me.FormDate) I might use

DateSerial(Year(me.FormDate), Month(Me.FormDate), Day(Me.FormDate))

Not pretty but it works.

 

[NevilleT]

I have had similar issues. Usually resort to one of two solutions. Either
put the date in a date variable before using it, or use DateSerial.

If I had a field on a form (Me.FormDate) I might use

DateSerial(Year(me.FormDate), Month(Me.FormDate), Day(Me.FormDate))

Not pretty but it works.

 

[José António Silva]

Well,
Since both CDate and DateSerial return the same data type – a date – I
believe that your solution is equivalent to mine.

I only have two doubts about your post:

1) Are you using Windows US and Access Run-Time US versions or not?

2) What do you do with this expression? Do you use it like this? … …… WHERE
SomeDate = DateSerial( “ & Year(me.FormDate)& “,†& Month(Me.FormDate) & â€,â€
& Day(Me.FormDate) & “)â€. I.E. you send the expression DateSerial to be
evaluated by ADE instead of sending to ADE the result (a date type) of the
DateSerial function. This later case will be equivalent to ….. WHERE SomeDate
= “ & DateSerial(Year(me.FormDate), Month(Me.FormDate), Day(Me.FormDate))

In any case, including my workaround with CDate, I suspect that these
expressions will enforce the whole table to be filtered by ADE instead of
SQL. This would lead to inefficiency problems. Don’t you agree?

 

[José António Silva]

Well,
Since both CDate and DateSerial return the same data type – a date – I
believe that your solution is equivalent to mine.

I only have two doubts about your post:

1) Are you using Windows US and Access Run-Time US versions or not?

2) What do you do with this expression? Do you use it like this? … …… WHERE
SomeDate = DateSerial( “ & Year(me.FormDate)& “,†& Month(Me.FormDate) & â€,â€
& Day(Me.FormDate) & “)â€. I.E. you send the expression DateSerial to be
evaluated by ADE instead of sending to ADE the result (a date type) of the
DateSerial function. This later case will be equivalent to ….. WHERE SomeDate
= “ & DateSerial(Year(me.FormDate), Month(Me.FormDate), Day(Me.FormDate))

In any case, including my workaround with CDate, I suspect that these
expressions will enforce the whole table to be filtered by ADE instead of
SQL. This would lead to inefficiency problems. Don’t you agree?

 

[NevilleT]

Hi Jose

I am not using US date format. Just completed an SQL back end database and
had to use the DateSerial approach on one form. It was actually a bit more
complicated as there was also a DateDiff in the formula. May not be the most
efficient but it did not noticebly slow things down.

 

[NevilleT]

Hi Jose

I am not using US date format. Just completed an SQL back end database and
had to use the DateSerial approach on one form. It was actually a bit more
complicated as there was also a DateDiff in the formula. May not be the most
efficient but it did not noticebly slow things down.

 

[GeoffG]

Hi José,

I have read your two threads on this subject.
I don't have an answer but I thought the following may help:

1.
http://support.microsoft.com/kb/173097

I found the above article by searching the Microsoft website
for:

"Microsoft Access" ODBC "SQL Server" date criteria
(Notice the quotation marks around phrases).


2.
Access 2000 Developer's Handbook
Volume 1 Desktop Edition
ISBN: 0-7821-2370-8
Authors: Ken Getz, Paul Litwin, Mike Gilbert
Publisher: Sybex
Page 537:
"...Just as in many other places throughout Access
(basically, any place working with dates and Jet), you must
format dates into the U.S. format (mm-dd-yyyy, or some
variation on that). In addition, all literal dates must be
enclosed in "#" characters..."

In the above quotation, the words "and Jet" seem significant
in your situation because they imply that, if you're not
using Jet, then this rule may not apply.

Incidentally, the above quote comes from a discussion on
using the FormatCondition object of a TextBox control on a
form. (The discussion shows how to use the FormatCondtion
object to turn the background colour of a TextBox to red
when the form's recordsource puts a date within the past
month into the TextBox.)

3.
Like you, when I have a problem, I think:

(a) I can't be the first person who has had this problem;
and

(b) the computer scientists at Microsoft must have
provided a solution.

As always, the problem is finding the solution - with the
help of the newsgroup community, the Microsoft website, the
Internet or reference books. As you no doubt know, this
often means asking yourself the right questions to eliminate
irelevant topics (not least because the Microsoft
knowledgebase is so vast).

From your descriptions - and this is just a hunch - I
somehow doubt whether the problem arises from the version of
Access, the version of Windows, or the facts that you're
using a compiled database or the Access run-time or the
Portuguese versions. If any of those were the source of the
problem, it seems the problem would be much more widespread.
The problem sounds as though it has something to do with
negotiating with SQL Server (as in 1 above). In saying
this, I acknowledge I've not taken into account all the
examples you mention.

I'm sorry I don't know the answer.
Good luck!
Geoff




"José António Silva"

 

[GeoffG]

Hi José,

I have read your two threads on this subject.
I don't have an answer but I thought the following may help:

1.
http://support.microsoft.com/kb/173097

I found the above article by searching the Microsoft website
for:

"Microsoft Access" ODBC "SQL Server" date criteria
(Notice the quotation marks around phrases).


2.
Access 2000 Developer's Handbook
Volume 1 Desktop Edition
ISBN: 0-7821-2370-8
Authors: Ken Getz, Paul Litwin, Mike Gilbert
Publisher: Sybex
Page 537:
"...Just as in many other places throughout Access
(basically, any place working with dates and Jet), you must
format dates into the U.S. format (mm-dd-yyyy, or some
variation on that). In addition, all literal dates must be
enclosed in "#" characters..."

In the above quotation, the words "and Jet" seem significant
in your situation because they imply that, if you're not
using Jet, then this rule may not apply.

Incidentally, the above quote comes from a discussion on
using the FormatCondition object of a TextBox control on a
form. (The discussion shows how to use the FormatCondtion
object to turn the background colour of a TextBox to red
when the form's recordsource puts a date within the past
month into the TextBox.)

3.
Like you, when I have a problem, I think:

(a) I can't be the first person who has had this problem;
and

(b) the computer scientists at Microsoft must have
provided a solution.

As always, the problem is finding the solution - with the
help of the newsgroup community, the Microsoft website, the
Internet or reference books. As you no doubt know, this
often means asking yourself the right questions to eliminate
irelevant topics (not least because the Microsoft
knowledgebase is so vast).

From your descriptions - and this is just a hunch - I
somehow doubt whether the problem arises from the version of
Access, the version of Windows, or the facts that you're
using a compiled database or the Access run-time or the
Portuguese versions. If any of those were the source of the
problem, it seems the problem would be much more widespread.
The problem sounds as though it has something to do with
negotiating with SQL Server (as in 1 above). In saying
this, I acknowledge I've not taken into account all the
examples you mention.

I'm sorry I don't know the answer.
Good luck!
Geoff




"José António Silva"