Date() vs Now() -- issues with accuracy (97)

[jacksonmacd]

The Acc97 database uses VBA to update fields in several tables using Date() and Now() functions. With Now(), it uses CurrentDB.Execute with an SQL statement, while with Date(), it populates a bound textbox. The database is used by multiple people, and is replicated across a WAN.

On one of the computers, it has updated one of the fields incorrectly -- the one using Now() in an SQL statement. When I check the actual field contents using CDbl(DateFieldName), the value is about 39488. By comparison, all the other entries in all the other tables are about 37537, which AFAIK is the correct Julian number for today's date.

Different computers use different Regional Settings (I am in English Canada, the other computer is in Quebec). My understanding was that the computer's regional setting didn't affect how the date was stored, only how it was displayed. However, there is an incorrect value stored in the table. And it is incorrect only in the table that used the Now() function in the SQL statement -- the Date() function in the bound textbox works properly.

How do I fix this problem? Thanks in advance.

[HansV]

37537 = 10/08/02 and 39488 = 02/10/08 (both US style).

If you have something like (simplified for demonstration purposes)
<font face="Georgia">CurrentDb.Execute "Update MyTable Set MyField = Now()"</font face=georgia>
you should be OK under all regional settings - SQL will evaluate Now() as a date and return 37537 plus a decimal fraction representing the current time.

However, if you have something like
<font face="Georgia">CurrentDb.Execute "Update MyTable Set MyField = #" & Now() & "#"</font face=georgia>
it will only work correctly if your date setting is US - since you are concatenating a string, the Now() function will be evaluated by VBA (not SQL) as a string in the local date setting, and subsequently SQL will interpret this string as a US date regardless of local settings.

Welcome to the world of incompatibility between VBA, SQL and local settings!

[jacksonmacd]

Bingo -- and I thought I was being so smart by including the "#" in the SQL statement. Actually, I thought it was required, and did not even consider omitting the "#".

I hadn't really appreciated the "division of labour" between SQL and VBA in the scheme of things. Thanks very much for your clear explanation.