Results 1 to 10 of 10
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    Importing 4 digit field as date

    I am working on an import spec from a text field and there is a field with 4 digits only. Is it possible to import this directly as a date field or will I need to import as number or text and then run a conversion to get it to a date field?


    Thanks

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How exactly is your date formatted? Is it simply month and date with no year (i.e. MMDD)?
    I don't know of any date setting on the Import Specs that will be able to figure out any data less than 6 characters, so you may need to import it as text and then perform your conversion on it.
    You could import your data to a temp table first, and then use an Append Query to convert the date and write the date, along with all your other fields, to your final table.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Ok I was afraid of that. It is just the four digits like 1312 - YYMM . I was able to get it to work with the 6 digits like you mentioned but not the 4. Is it best to import as text or a number if I am going to later run some kind of query to convert it to date. I would like it to be a real date field so I imagine I will have to add some kind of standard day of the month and then convert it to a date field.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    When having to convert values, I always like to read them in as text. That was you can ensure that things like leading zeroes do not get dropped.
    Last edited by JoeM; 06-12-2013 at 08:02 PM. Reason: typo

  5. #5
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I found that while trying to read in the 4 digit field as a date it gave me a conversion error and would not actually import any data. Is there any way to force it to bring in the data as a date

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I apologize, I made a big typo in my last post.
    I meant to say "When having to convert values, I always like to read them in as text..."
    instead of "When having to convert values, I always like to read them in as date..."

  7. #7
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Thank, you I was thinking it might just be a typo. Could you please explain why? I have several fields I have to do this with and I would like to have a consistent valid reason that I can apply to all of them.
    Thanks in advance,

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It just makes working with the data easier. If you import them as numbers, leading zeroes will be dropped, where if you import them as text, they will not.

    Let's take a look at a few examples of dates in your YYMM format:
    1306
    0809
    1112
    0901


    If you import them as text, they will all import exactly as seen above. However, if imported as numbers, they will import as follows:
    1306
    809
    1112
    901


    See now how some entries are three characters long, while others are four? It may make your date conversions a bit trickier if you need to account for both possibilities. However, if you import as text, they are all exactly four characters long, so the conversion should be more straightforward.

  9. #9
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    Yes, I see. Thank you very much. This kind of thinking can save a lot of trouble later down the line. I appreciate the help and the explanation. I will file this away for future reference.

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Your welcome, glad to help!
    I am always happy when people want to understand "why", and aren't just looking for quick answers. It shows they really want to learn!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-31-2012, 07:53 AM
  2. Trouble importing date from CSV
    By NickP in forum Import/Export Data
    Replies: 1
    Last Post: 01-13-2012, 04:12 AM
  3. Entering 16 digit numbers into number field
    By chrismid259 in forum Access
    Replies: 7
    Last Post: 12-14-2010, 10:40 AM
  4. Problem with date when importing
    By ds_8805 in forum Import/Export Data
    Replies: 5
    Last Post: 01-27-2010, 02:30 AM
  5. Showing date and time when importing to access
    By timpepu in forum Import/Export Data
    Replies: 0
    Last Post: 05-07-2009, 07:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums