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.
|
Similar Threads
