Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Formating Number to Date

    I have a number field (which is dates) formatted as YYYYMMDD and I'd like to convert it to date as MMDDYYYY. I can't seem to find the right answer on google. Would CDATE work?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Doubt it, but it would be easy enough to test.

    You can use the DateSerial() function along with the Mid() function to pull out the appropriate bits.
    Paul

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    before doing that check the underlying datatype of the column in the table. bear i9n mind that there is a wolrd of difference between how the data is stored (assuming its designed 'properly') and the way data can be displayed.

    if its date time then use and appropriate format statement
    if its not, then consider changing the datatype in the db so, if / as, when you need it you can take advantage of the inherent date/tome functions.
    otherwise judicious use of the string functions (as alluded to by Pbaldy) woudl do the job. something like...

    Code:
    mid(MyDateColumn,5,4) & left(MyDateColumn,4)
    should do the job
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2012
    Posts
    126
    Thank you. Im pulling the data from the AS400 we have. Its formated as a number not date and im making it a table with an append query. I'm going to try a few things here and appreciate all the help guys

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depends how you are doing your import
    you can either import direct into Access into a temporary table and then use an INSERT INTO query, doing what ever reprocessing you need to.
    or
    read the file using VBA and repocess before inserting into the table(s).
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Aug 2012
    Posts
    126
    Im using the insert into query. Thats how I usually do it.

Posting Permissions

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