Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More Dates(97) (Access 97)

    I've just typed the following into the debug window :-

    ? date
    07/05/02

    ? len(date)
    8

    ? now
    07/05/02 10:15:54

    ? left(now,8)
    07/05/02

    ? date = left(now,8)
    False

    ? left(date,8) = left(now,8)
    True

    Why does 'date = left(now,8)' show as false ? I know that 'date' has a length of 8 - it's just told me.

    I'm using Access97, with UK dates.

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

    Re: More Dates(97) (Access 97)

    There is a difference between the way a date or time is stored and the way it is displayed.
    Dates and times are stored as a number representing the number of seconds that have passed since midnight on December 30, 1899.
    If you display a date or time, Access by default uses the short date and time formats from your Windows settings.
    By applying the Len or Left function, you force Access to convert the date or time to a string.
    In the comparison Date = Left(Now, 8) the left hand side is a date value (a number) and the right hand side is a string, so it evaluates to False.

    I hope this is clear enough.

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Dates(97) (Access 97)

    Yes, that's all clear now, thanks very much.

    Would I be safer converting all UK dates to US format before calculating differences ? I know that I have to do this for SQL statements, anyway.

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

    Re: More Dates(97) (Access 97)

    If you use literal dates (constant values like #1/1/2002#) in VBA or SQL, you must use US format.

    In query design view, you can use dates in local format. Also, if you have a text box on a form, you can enter dates in local format. Access (or Windows) will convert them to the internal (numeric) format.

    But if you use the value of a text box in VBA to build a SQL statement or WhereCondition with literal date values, you must provide the conversion yourself. I often use the BuildCriteria function for this.

    I suspect this may not be entirely clear. Perhaps some examples help:

    Say you have a form frmMyForm with a text box txtDate. You want to open a report rptMyReport. Only records with MyDate equal to the date entered in txtDate should be included.

    DoCmd.OpenReport "rptMyReport", acViewPreview, , "MyDate=Forms!frmMyForm!txtMyDate"
    should work. The date in txtMyDate is used as a variable.

    DoCmd.OpenReport "rptMyReport", acViewPreview, , "MyDate=#" & [txtMyDate] & "#"
    will return incorrect results. The date in txtMyDate is treated as a literal, so 1/12/2002 (UK) will be interpreted as 12/1/2002 (US).

    DoCmd.OpenReport "rptMyReport", acViewPreview, , BuildCriteria("MyDate", dbDate, [txtMyDate])
    should work. The date in txtMyDate is treated as a literal, but BuildCriteria takes care of the conversion.

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More Dates(97) (Access 97)

    Thanks, Hans.

    I've always struggled with dates in Access, as Microsoft assume they will be US format, but you've made it a lot clearer. I've never used BuildCriteria, but I'll certainly give it a try now.

Posting Permissions

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