Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Problems with Date format

    Hi I am going bonkers here! I am trying to prompt user for date (age of data) and then to insert the date into a table. The prompt works fine but when the date appears in the table it changes format.... Basically I put this into the prompt:"01/03/09" and in the table it says "03/01/09". From this I conclude that the prompt must be in american date format and the table is in British format.... but how do I make the two both assume british format?????? here is my code:

    DoCmd****nSQL ("ALTER TABLE tbl_holding add date_updated date;")
    DoCmd****nSQL ("ALTER TABLE tbl_holding add DataSource text;")

    Dim monthdate As String
    Dim prompt As String

    EnterDateLabel:
    prompt = "Please enter date of Exchange Rate File in xx/xx/xxxx format."
    monthdate = InputBox(prompt)
    If IsDate(monthdate) = False Then
    MsgBox "Please enter valid date in the correct format (xx/xx/xxxx)"
    GoTo EnterDateLabel

    End If


    DoCmd****nSQL ("UPDATE tbl_holding SET date_updated = #" & monthdate & "#;")

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    JET knows only American or ISO dates so if you are form the civilised world you have to convert the date to one of those formats
    eg
    mydatecolumn = format(adatevalue,"#MM/DD/YYYY#")
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    if you are form the civilised world you have to convert the date to one of those formats
    Just FYI, the French use dd/mm/yyyy too so this point isn't strictly true
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Dates on this planet are actually quite retarded tbh. Different countries with different formats of the exact same thing? Months which have different numbers of days... months called October and December should be the 8th month and 10th month respectively but because some morons insist that a month be named after them, they are the 10th and 12th months?!?

    You humans are so weird in this regard.

    Gene Rodenberry had the right idea for dates. The stardate is so simple and functional.

    All dates on this planet should be changed to yyyy/mm/dd at least. Then they would even sort correctly if in a text field
    Last edited by StarTrekker; 07-29-09 at 22:43.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Just FYI, the French use dd/mm/yyyy too so this point isn't strictly true

    good point and well made

    The ISO standard of YYYY/MM/DD dates back to the early 1970's
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Point for StarTrekker:
    The Roman year started in March, rather than January. Therefore September, October, November and December were the 7th, 8th, 9th and 10th months.

    I'm with you on the variable number of days, though. We need to move the planet a little closer to the sun, so as to get 12 months of 30 days...
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or just not use months ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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