Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    6

    Unanswered: date values being stored improperly

    hey guys hopefully this is something simple you can help me with..

    im trying to update a table using a form which runs sql queries when the user presses the submit button..

    i have a calendar control which returns the proper date format (mm/dd/yyy)
    however if the day part is < 12, it reverses the mm/dd

    ie, for July 1st, 2006: 01/07/2006 becomes 07/01/2006
    however, the 15th of july: 15/07/2006 stays as 15/07/2006

    using stepthroughs while debugging the code, i can see the SQL command is sending the value properly as 01/07/2006
    the field is set int he table as short date, and my system properties has short date set as dd/mm/yyyy

    Any ideas on why its flipping them or a quick fix on how to make it stop?

    Thanks!

    J

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Check your regional settings ... Sounds like your in England (at least windows thinks so) ... Set it to US.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2005
    Posts
    6
    Hey actually I am in Canada and would prefer to keep the format dd/mm/yyyy since all the other db's we use are in that format too

    setting standards to English (United States) does keep the format consistently mm/dd/yyyy... just when its in the English (Canada) standard is when it seems to switch it on me....
    Unfortunately all of our computers are set to English (Canada) and other apps depend on that format so Ill have to find a way around it..


    Thanks for taking the time to help though
    Last edited by jenke; 04-24-06 at 17:05.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1

    Date Format

    Hi

    I do not know if this is relevant to Canada (or whether I am missing somthing in the UK, but to make dates work correctly in code (either as Form RecordSources, ADO/DAO recordsets or action queries to add/updatetables) I always have to format the dates in US format.

    For example

    Code:
    SQL = "SELECT * FROM qryEstimatorTimeSheetRpt WHERE WeekEndingDate BETWEEN #" & Format(txtFromDate, "mm/dd/yy") & "# AND #" & Format(txtToDate, "mm/dd/yy") & "#;"
    Obviously my machine is set to UK date format and all forms/table display correctly in this format, but if I do not do the above then, as indicated by jenke, some dates get changed!!

    I also need to do this is Excel when using ADO etc. in code, but NOT when using the Access query designer.

    My reasoning is that the query designer 'looks up' the machine country setting and compensates, but VB does not and ALWAYS assumed US date format !!??


    Am I missing something to make this requirement redundant ??

    MTB

  5. #5
    Join Date
    Apr 2005
    Posts
    6
    Hey MTB you may be onto something..

    Thanks for the tip it works great.. and to be honest thats all i really care about at this point..



    J

Posting Permissions

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