Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    42

    Question Unanswered: Day and month are switched in query

    Hi,

    I have a strange problem when I'm making a query.

    In the design view of the query, I say: "...date = #10/02/2004#".
    When I look in the sql view of that query, the day and month are switched: "...date = #2/10/2004"

    I took some screenshots, put them in a word doc and attached it to this thread.

    Does anyone has an explanation ( and solution) for this problem?

    Thx,

    Koen
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    To get to the bottom of this problem, a bit of history:

    When Microsoft bought Access from the company who originally created it (and in those days it was called Cirrus), the product used ANSI SQL. In ANSI SQL, only American date formats are allowed.

    Microsoft wanted to sell Access worldwide, so tweaked the query design window so that you can enter a date in your regional date format (for example dd-mm-yy, as we do in Britain) and Access would convert this into the ANSI mm-dd-yy format. Which it does. So, however you enter your dates in a query, its SQL will only display American dates, because that's all that SQL understands and executes. This is also why, when you summarise data using DSum or any of the Domain functions in a query or code, you have to type the dates in in American format.

    Problem solved.

    Hope this helps.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    In ANSI SQL, only American date formats are allowed.
    that statement is, um, how shall i put this, wrong

    american formats may have been the only formats Cirrus allowed, but ansi sql allows, among other things, the ISO standard format yyyy-mm-dd

    access does not convert dates to american format, it stores dates internally as whole integers

    furthermore, you do not have to type dates in american format in access

    access understands, without ambiguity, the yyyy-mm-dd format wherever you use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    42

    Question

    Thx both for your explanation but there is a little thing that I don't understand.

    When I make a query in design view :
    SELECT Number
    FROM table
    WHERE (((Date)=[Forms]![frmPV]![txtTempDate]));

    So I replace de date 10/02/2004 by a field where the same date is in.

    The data of the field "txtTempDate" is also "10/02/2004"
    => then the sql is working OK !

    Strange?

  5. #5
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    Not sure I'd agree. Try doing a .FindFirst in code using British date formats and you'll see it doesn't work. Microsoft themselves say this. In other words, yes, you can type British dates into the criteria row of a query, but wherever else you query using dates, they have to be in American format. Perhaps you've never tried querying on a British machine?

    Originally posted by r937
    that statement is, um, how shall i put this, wrong

    american formats may have been the only formats Cirrus allowed, but ansi sql allows, among other things, the ISO standard format yyyy-mm-dd

    access does not convert dates to american format, it stores dates internally as whole integers

    furthermore, you do not have to type dates in american format in access

    access understands, without ambiguity, the yyyy-mm-dd format wherever you use it
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    indeed
    Perhaps you've never tried querying on a British machine?
    it is on my list of things to do before i die, but i gotta be honest, it's not near the top
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by andybriggs ... but wherever else you query using dates, they have to be in American format.
    wherever else? dates do not have to be american in SQL View, because i write queries with iso standard format date strings all the time and have never had the slightest problem

    perhaps the british machine has a virus?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    New Zealand HERE

    Have the same problem

    But my work round is to

    have written a function
    Code:
    Function USA(INDATE As Date)
    USA = Month(INDATE) & "/" & Day(INDATE) & "/" & Year(INDATE)
    End Function
    So

    when I Write SQL

    SQL = ""
    SQL = SQL & "SELECT Number"
    SQL = SQL & " FROM table"
    SQL = SQL & " WHERE (((Date)=#" & USA(me.Pdate) & "#));"

    Hasn't let me down yet
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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