Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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!

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •