Stumped By Dates

Discussion in 'Access Forms Coding' started by TeeSee, Jun 8, 2012.

  1. TeeSee

    TeeSee Guest

    Access 2003, WIN 7, Regional settings= English, Canada

    No matter what I try formatting date field in tables, formatting or
    not during insert to tables using strDate = "#" & mydate & "#" Icannot
    get the table updated to dd-mm-yyy. The following is a trial SQL print
    out that looks correct and Insert to the table okay but in mm/dd/yyyy
    format

    INSERT INTO tblDates(dtmDate1,dtmDate2)VALUES(#04/06/2012#,
    #11/06/2012#) .... The dates are the fourth and eleventh of June 2012.
    When inserted to the tbl they show as 06/04/2012 etc.
    This seems to have started when I changed from XP to Win 7
    Could someone please advise how to correct.

    Thank you
     
    TeeSee, Jun 8, 2012
    #1
    1. Advertisements

  2. The reason you cannot get the Table updated to "dd-mm-yyy" is that dates are
    not stored as characters in formatted form -- they are stored as a number...
    looks like a double precision floating point, but for dates, interpreted
    with the whole number part representing date as number of days since Dec.
    30, 1899 and the fractional part representing seconds since last midnight.

    What is presented to you is that number with a format applied... either your
    regional format, format in the table, format applied to the control on a
    form, or a specific format used in a function. And many of us (I, for one)
    have to review the precedence of the formats when we don't see what we
    expect.

    Unless someone has "slipped a change by me" in some recent version, if you
    specify a date in a Query, you must use US date format, that is mmddyy or
    mmddyyyy.
     
    Access Developer, Jun 8, 2012
    #2
    1. Advertisements

  3. TeeSee

    TeeSee Guest

    Larry .... Many thanks for responding.May I respond by addressing each
    of your paragraphs.

    Par. 1 I believe I understand

    Par 2. I am using Allen Brownes ajbCalendar to populate an unbound
    text box and it is NOT formatted. AfterUpdate the text box shows the
    date as dd/mm/yyyy. There is no formatting in the SQL statement as
    above and there is no formatting in the table. I have expanded my
    TRIAL DB as follows:
    strSQL = "INSERT INTO tblDates" _
    & "(dtmDate1,dtmDate2, dtmDate3)" _
    & "VALUES" _
    & "(#" & Format(Me.dtmDate1, "dd/mm/yyyy") & "#, #" &
    Me.dtmDate2 & "#" & ", " & CLng(dtmDate3) & ")"
    Date 1 and Date2 both go into the table as mm/dd/yyyy and of course
    Date3 goes in as an integer 41078 as it happens. Debug.Print as
    follows

    INSERT INTO tblDates(dtmDate1,dtmDate2, dtmDate3)VALUES(#04/06/2012#,
    #11/06/2012#, 41078)

    Again my system date formats are SHORT: dd/mm/yyyy and LONG: mmmm-dd-
    yy

    Does it make any sense to store all dates as integers (In this case I
    am NOT dealing with the time part) knowing that you can more easily
    format them as required on forms and reports?

    Having said all of that there MUST be something else going on since I
    have hundreds of records that have been saved in the same table that
    are correct

    Par 3: In your third paragraph, I think, you are suggesting that since
    I am using SQL which is a query then I should be formatting it as mm/
    dd/yyyy anyway. Okay but that is how it is being saved. I created a
    BOUND test box to dtmDate2 and tried to format is as dd/mm/yyyy and
    there was no change. If I format it as Shrort Date, Medium Date or
    Long Date it does format to those. Date3 stores as integer can be
    formatted any way I choose.

    Still using OLDE access 2003. the only NEW thing is Win7

    Thanks again for your response. I hope all this makes sense but
    unfortunately I am no further ahead at the moment.
     
    TeeSee, Jun 8, 2012
    #3
  4. If you are going to perform calculations on, or going to manipulate, a
    date/time value, it makes sense to store it as Date/Time data type. If you
    are just keeping it for the user to view, especially if you want it in a
    particular format, it makes sense to store it the way you want to display
    (e.g., preformatted into text).
    Nothing wrong with that. Some of us just consider that Access 2003 is "the
    latest version of Classic Access" (before the user interface was
    "ribbonized" in Access 2007). Only caution is that, Real Soon Now, Microsoft
    will be dropping all support, even security updates, for Access 2003. On the
    other hand, I know a few people who are still using Access 97 as their
    primary version, because they are convinced that it is the _best_ version
    ever released, and Microsoft support has not been available for some years.
    Well, I'm not the best authority on date manipulation. I've worked on only a
    few "international" applications, and all of those were "US English only",
    not multi-language.

    And, because of the effect of regional settings, changing Operating Systems
    could affect the way dates are formatted.
     
    Access Developer, Jun 8, 2012
    #4
  5. In addition to what Larry's told you, see what Allen Browne has at
    http://www.allenbrowne.com/ser-36.html


    "TeeSee" wrote in message

    Access 2003, WIN 7, Regional settings= English, Canada

    No matter what I try formatting date field in tables, formatting or
    not during insert to tables using strDate = "#" & mydate & "#" Icannot
    get the table updated to dd-mm-yyy. The following is a trial SQL print
    out that looks correct and Insert to the table okay but in mm/dd/yyyy
    format

    INSERT INTO tblDates(dtmDate1,dtmDate2)VALUES(#04/06/2012#,
    #11/06/2012#) .... The dates are the fourth and eleventh of June 2012.
    When inserted to the tbl they show as 06/04/2012 etc.
    This seems to have started when I changed from XP to Win 7
    Could someone please advise how to correct.

    Thank you
     
    Douglas J Steele, Jun 9, 2012
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.