Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 97 UK/US Date Formats

    I'm trying to use the WHERE condition of the docmd.openreport action which requires dates to be in US format (mm/dd/yy). As I and my users are in the UK, the system settings are all UK (dd/mm/yy).

    I'm using the following to pass US formatted dates to my WHERE statement:

    Format (me.txtFrom, "mm/dd/yy")

    but for some reason it only formats to US format if the day part is below 10 (i.e. 06/05/01 (6th May 01) converts fine to 05/06/01, but 26/05/01 (26th May 01) stays as 26/05/01).

    Can anyone help me on this, it's driving me nuts!!!!

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 97 UK/US Date Formats

    have you tried using the CDate() function to convert the value into a date instead of using format? CDate should convert it to whatever format your system uses.
    Charlotte

  3. #3
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 UK/US Date Formats

    Yeah, I tried that but as my PC locale setting is English (UK), it sets it to dd/mm/yy format (rather than mm/dd/yy). Still driving me nuts!

  4. #4
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 UK/US Date Formats

    Eureka, have fixed it.....

    Rather than formatting my date variable i.e.
    datFrom = Format(me.txtFrom,"mm/dd/yy")

    If I pass the unformatted datFrom to my SQLstring and Format it as part of that it seems to work i.e. Format (datFrom,"mm/dd/yy").

    Thanks for your help anyway,

    Cheers

    Ade

Posting Permissions

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