Results 1 to 2 of 2

Thread: Dates in Access

  1. #1
    Join Date
    May 2011
    Posts
    1

    Unanswered: Dates in Access

    I have an issue with dates in Access. I have dates formatted as 040114 and 090111 and want to format them as 4/1/14 and 9/1/11. I used Format(CDate([DATE]),"mm/dd/yy") and Format([DATE],"mm/dd/yy") and got 10/28/09 and 09/17/46 as the results. What's up with that?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you have stored the dates as text / string then the format has nothing what so ever to do with the storage. you can format a date value in any way or style you want or are used to. fer instance you can display the same date as an ISO date (yyyy/mm/dd), a real date (dd/mm/yyyy) or if you are out in the boonies mm/dd/yyyy, you could replace the month number with the local language reprsentation of that month eg 01 Apr 2011 and so on

    Access / JET like most DB / data storgae systems uses a numeric representation for dates (IIRC its a two part number, the integer part identifies the number of days since Jan 1900, the decimal part indicates the time element 0.5 = 12 midday, 0.75 = 18:00:00 and so on)

    so it comes down to how you store the data..
    is it stored in a date/time column
    or
    is it stored as text

    even if you have broken one of the cardinal rules of database design you can still format them in the bastardised date format used in the US
    eg
    myStringDate = left(mycolumn,2) & "/" & mid(mycolumn,2,2) & "/" & mid(mycolumn,4)


    The Access Web - The Ten Commandments of Access
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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