Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Posts
    7

    Red face Unanswered: Query with dd/mm/yyyy returns records with mm/dd/yyyy

    Hi All,

    Hope there's someone who can help me out with this strange problem.

    I am trying to run a query in Access with a simple sql string

    below is my code

    SQL = "Select * from stock where sdate = #11/12/2013"

    Set qdf = CurrentDb.CreateQueryDef("secondqry", SQL)

    DoCmd.OpenQuery "secondqry", acViewNormal, acReadOnly

    The result returns with the record with the date 12/11/2013 <----wrong

    if I run the query with the date 12/11/2013, the result records are with date 11/12/2013 <----wrong

    if i run the query with the date 28/06/2013, the results records return with 28/12/2013 <--- no problem with such kind of very obvious formatted date.

    Some basic info:

    my Date field in the Access Table is Short Date
    I created the query in the Access database and run there to see the results and results seem ok.

    only when passing from the Codes that is somehow changing the date dd/mm/yyyy to mm/dd/yyyy

    Strangely enough, 28/06/2013 is not giving any problem and giving me correct results.


    Any idea?

    Any suggestion would be greatly appreciated...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest using the SQL standard format fir dates which is yyyy/mm/dd.
    I guess you have a mismatch between tbe computer and office localisation
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    7
    Hi Healdem,

    Thanks for your fast reply..

    even if i change the date to yyyy/mm/dd in sql string .. and in the design format of the database field, the results are still giving me the wrong..

    query with 2013/12/11 returns 2013/11/12 vise versa

    at the same time.. 2013/06/25 will give out the correct result though.

    Any help? I have no idea what is happening here.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you don't need to change the format in the db, bear in mind the db stores dates using its own format. (FWIW its a double precision number). the format is purely for display purposes. the suggestion concerning iso date format is whewn you have to supply a date literal as part of a query
    eg
    Code:
    SQL = "Select * from stock where sdate = #2013/12/11#"
    I can't tell if you omitted the closing #, all date literals (examples) MUST be delimited by #

    dates can be displayed in any format you like, if your preferred format is dd/mm/yy then use it, but be aware that Access is US centric, so it can interpret literals you supply inocrrectly. using the ISO date works in all countries, all installations and is the preferred (and standard) date format in all SQL db's. its part of the ISO SQL specification

    check your computers internationalisation settings (ie it is set to dd/mm/yy IF THAT is what you want/need/expect). some versions of office also have an internationalisation section

    then check your data
    for know I'd suggest running a query which displays dates as dd mmm yyyy, that way round you can see the actual date values
    if you have data errors fix them

    28/06/2013 is obviously 28th June, but 10/11/2013 is ambiguous it could be 10th November, it could be 11th October, but 2013/10/11 can only ever be 11th October
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2013
    Posts
    7
    HI Healdem,

    I didn't omit the #, it was just missed out in pasting here

    I have tried with yyyy/mm/dd from query with the dd/mm/yyyy in the database but it gave the same wrong results.

    I tried with dd mmm yyyy from query and it also returned the wrong date records.

    Would I need to change the computer settings and the office settings?

    Thanks a lot for helping me out here.

  6. #6
    Join Date
    Sep 2013
    Posts
    7
    I was trying out something..

    Below is my code

    Dim tDate

    tDate = #12 Nov 2013#

    Once I hit enter, it is automatically, (in VB coding window) changed to tDate = #11/12/2013#

    There has to be something wrong here even before parsing as SQL string to run query to the database.

    Thanks a lot

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before check your settings
    vb, vba,vb.net is US centric
    right now I don't understand where its going worng
    yoiu need to check your data is correct
    then you need tocheck why ytour query isn't doing what you expect.

    hence why I suggested you check the data using dd mmm yyyy
    then correct any errors

    then once you know your data is correct then you can move onto why you are getting duff data, check the input, check the internationalistation settings

    then you cna move on to your queries. if you use a datepicker control your problems go away as the date picker control handles the translation issues in background
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2013
    Posts
    7
    HI Healdem,

    Thanks for your reply.

    Finally, I came to notice that, somehow the data passes from text box or even in the codes, the dates are in mm/dd/yyyy format ( I tried changing the international settings of Microsoft Office and my local machine but it didn't work at all).

    So having that in mind, I take it and change it to ddmmmyyyy format and query the tables. It works..

    Thank you very much for your help.

Tags for this Thread

Posting Permissions

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