Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date in query (Access 2000)

    I would like to know if I have this SQL statement:

    SELECT qryTransMonthEnd.InvoiceDate, *
    FROM qryTransMonthEnd
    WHERE (((qryTransMonthEnd.InvoiceDate)>=#02/10/01# And (qryTransMonthEnd.InvoiceDate)<=#02/10/31#));

    why in the query design mode the criteria row will change it to

    >=#01/02/10# And <=#31/02/10#

    My spec on the SQL was right 2002 Oct 1, and 2002 Oct 31. In my computer setting, the date is set to yy/mm/dd

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date in query (Access 2000)

    SQL uses American date format and ignores regional settings.

    HTH

    Peter

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date in query (Access 2000)

    To amplify a bit on Peter's response, you are using a short date format, which can be the source of trouble. Access stores all dates internally in the same format regardless of the regional date settings. In that format, it uses a floating point format to store the number of days since 31 December 1899 as the integer portion, and the time from 12:00:00 AM (midnight) as the decimal part of a number. So all display formats have to convert from what you've given to that format. In addition, the SQL engine only works with dates in US format, since all dates are stored in one format anyhow. In your case, you probably have a European regionalized version of Office, so it expects dates to be in the format Day/Month/Year and tries to compensate for that based your date settings and gets it wrong. Try putting in a four character year, i.e. 2002, and see what happens.
    Wendell

Posting Permissions

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