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.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

    "TeeSee" <> wrote in message
    news:...
    > 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
     
    Access Developer, Jun 8, 2012
    #2
    1. Advertisements

  3. TeeSee

    TeeSee Guest

    On Jun 8, 1:00 pm, "Access Developer" <> wrote:
    > 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.
    >
    > --
    > Larry Linson
    >   Microsoft Office Access MVP
    >   Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
    >
    > "TeeSee" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > 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


    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. "TeeSee" <> wrote

    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).

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


    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.

    > Thanks again for your response. I hope
    > all this makes sense but unfortunately I
    > am no further ahead at the moment.


    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.

    --
    Larry Linson
    Microsoft Office Access MVP
    Co-Author, Microsoft Access Small Business Solutions, Wiley 2010
     
    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
    news:...

    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

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.
Similar Threads
  1. Fysh1

    Stumped on listbox process.

    Fysh1, Jul 17, 2003, in forum: Access Forms Coding
    Replies:
    2
    Views:
    85
    John Vinson
    Jul 18, 2003
  2. Kent Wolf

    Stumped, Desperate: Word 97 with Acc 97 on WinXP...

    Kent Wolf, Feb 5, 2004, in forum: Access Forms Coding
    Replies:
    1
    Views:
    121
    CSmith
    Feb 5, 2004
  3. dave

    Stumped: summing amounts on a subform

    dave, Jun 26, 2004, in forum: Access Forms Coding
    Replies:
    3
    Views:
    107
    Ken Snell
    Jun 27, 2004
  4. SharonInGeorgia

    Stumped on Syntax

    SharonInGeorgia, Feb 23, 2006, in forum: Access Forms Coding
    Replies:
    4
    Views:
    148
    Klatuu
    Feb 23, 2006
  5. Rick Allison

    3167 "Record is deleted" - I'm stumped

    Rick Allison, Mar 26, 2006, in forum: Access Forms Coding
    Replies:
    1
    Views:
    233
    Ken Snell \(MVP\)
    Mar 26, 2006
  6. Carrie

    Stumped on List Box Code - Null Values

    Carrie, Apr 18, 2006, in forum: Access Forms Coding
    Replies:
    0
    Views:
    113
    Carrie
    Apr 18, 2006
  7. -Mark Shelby

    Listbox: I am stumped!

    -Mark Shelby, Jun 6, 2006, in forum: Access Forms Coding
    Replies:
    4
    Views:
    91
    Dale Fye
    Jun 7, 2006
  8. jln via AccessMonster.com

    Im stumped on how to best display data

    jln via AccessMonster.com, Oct 11, 2006, in forum: Access Forms Coding
    Replies:
    0
    Views:
    90
    jln via AccessMonster.com
    Oct 11, 2006
Loading...