Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update date format (2k)

    one of my friends has a database w/ dates stored as 110404 for 11/04/04. I guess he didn't know you could select "date/time" format in table formatting, but eitherway he has decided to change his date format to one that access can understand. I told him he should probably use an update query to do this, but I'm not really sure how to go about doing this. he described how he entered his date like this to me:

    The list includes dates as numbers in the format *MDDYY, where "*" is "1" if the month is October through December, otherwise "*" is empty. For example, November 3, 1977 is 110377, January 3, 1977 is 10377, and January 3, 2004 is 10304.

    can this be done in a query? i think i know the logic behind it in terms of code... but don't know how to build it in a query.

    newdate: if(len([olddate])=5, instr([olddate], 1) + "/" + .... ?

    or are there conversion functions for this type of thing?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: update date format (2k)

    You could use the expression

    <code>NewDate: DateValue(Left([OldDate],Len([OldDate])-4) & "/" & Mid([OldDate],Len([OldDate])-3,2) & "/" & Right([OldDate],2))</code>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: update date format (2k)

    It will be easier if you do this with 2 queries, rather than use a complicated IIF statement.

    First of all, backup the database.

    Second, change the name of the old field in the table, If it was [EntryDate], change it to [oldEntryDate]. Then create a new date field named [EntryDate].

    Your 1st update query (for 6-character dates) would be (assumes oldEntryDate is a text field):
    UPDATE yourTable SET EntryDate = Cdate( Format([oldEntryDate],"@@/@@/@@")) WHERE Len(oldEntryDate) = 6
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: update date format (2k)

    There isn't an explicit conversion function for this sort of thing, but you can use the string functions to do such a conversion. The most useful is the Mid() function and you can create an expression, much as you have started, in the update grid of the query designer. One thing you might want to do to be sure the result is treated as a data is to prefix and suffix the value with the pound (#) sign. You may well have some dates that are garbage as well - so some manual conversion may be necessary. The trick is to create a new date/time field in the table, and then use an update query to put the expression in selected fields based on an IIF statement.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update date format (2k)

    aha! thanks guys. I also found you can also copy it into excel and perform the conversions there, and then import it back into the database. i should mention it was a 1 time thing (atleast i hope it is for his sake).
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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