Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing into Access (Dates, sorta) (Access/Excel 2000)

    I am curious to learn if there is a method, macro, VBa or easier statement that will help me change a date that has been typed like this in Excel before importing to Access as a table?

    20000330

    which is yyyymmdd without any formatting whatsoever...The column has been formatted as General, so of course Access doesn't know its a date column. Also the method I have been using that HansV gave me earlier for fooling Access into seeing something different (as it looks at the first 5 or 6 rows of the imported data to distinguish its characteristics) has worked well with text and numbers thus far by inserting a new row above Excel data just below the field names and either typing the letter A for text or the number 2 for a number field does not allow the same result when I type a date such as 2000/03/03 in the inserted line. It still doesn't look at the imported column as dates. When in Access maybe there is something I can do to tell Access that this imported field is a date, and change it once imported. It doesn't matter. Before importing or after importing into Access (the simplest solution is the key here...if there is one). Hate for this student who needs the answer to have to retype all his dates in excel as dates before importing...there are at least 6 sheets with 900 entries on each sheet. So that would make him type at least 5400 new dates before sending the data to Access.
    Thanks for any help here on this...sincerely
    Nannette Padgett
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Importing into Access (Dates, sorta) (Access/Excel 2000)

    There is no way that Access (or rather Windows) will recognize 20000330 directly as a date. Import the column as numeric, and then use a query to calculate the date. Say that your column is named MyDate; in the query design grid add a field

    ConvertedDateateSerial(Left([MyDate],4),Mid([MyDate],5,2),Right([MyDate],2))

    ConvertedDate will be the name of the new field - replace it by whatever you prefer. In most cases, a selection query will suffice - you can use this query for forms, reports etc. But if you would prefer to have the dates in the table, add a date field to the table and create an update query that sets this date field to the expression used above:

    DateSerial(Left([MyDate],4),Mid([MyDate],5,2),Right([MyDate],2))

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing into Access (Dates, sorta) (Access/Excel 2000)

    Thank you ever so much Hans...
    We shall give this a go, and I have no doubts about it...[img]/forums/images/smilies/smile.gif[/img]
    Nannette
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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