Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing from Excel (Win XP Access 2003)

    I have an Excel table with date formats of dd.mm.yy. This is a date, but the Excel format is General. How can I import this to Access as a date?

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

    Re: Importing from Excel (Win XP Access 2003)

    You can either add a column to the Excel sheet with formulas that calculate the date from the text values, or import the sheet "as is" into Access, and create a calculated column in a query based on the imported table.

    BTW Were the replies to <post:=648,466>post 648,466</post:> and <post:=661,499>post 661,499</post:> helpful?

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    Of course the replies to the date picker and accum totals questions were useful. They always are. Thank you.

    Further on the Excel dates, could you show me an examp of how to convert the "as is" date in an Access query to a date format?

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

    Re: Importing from Excel (Win XP Access 2003)

    For example, with XLDate as the name of the Excel column:

    TheDate: DateSerial(1900+Val(Right([XLDate],2))-100*(Val(Right([XLDate],2))<30),Val(Mid([XLDate],4,2)),Val([XLDate]))

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It's nice if you post a short reaction to a reply - that way other Loungers know whether the problem was solved (or not, of course) <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    My bad. The year is yyyy, not yy. For example, 23.07.2006. Would that eliminate the first part of the expression where 1900 is added to the (Val(Right([XLDate],2)) expresion? I am having problems with the parenthesis. Too many or too few I feel dumb.

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

    Re: Importing from Excel (Win XP Access 2003)

    It would become

    TheDate: DateSerial(Val(Right([XLDate],4)),Val(Mid([XLDate],4,2)),Val(Left([XLDate],2)))

  7. #7
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    Yes!!!

    Many thanks.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    The TheDate field in the query responds to different date formats; howecer, I can't apply criterias. For example,between two dates does not function. How can I select certain dates from the query?

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

    Re: Importing from Excel (Win XP Access 2003)

    You'll probably need to create a new query based on the query in which TheDate is defined. To calculate TheDate *and* to apply criteria to it in one step is too complicated for the query engine.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    I created a new query based on the original query (where the TheDate field is calculated). When I enter a date criteria in the new query I get a data mismatch error. The date formats work OK on TheDate.

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

    Re: Importing from Excel (Win XP Access 2003)

    Are there records in which the date from Excel is blank? That could cause this error.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing from Excel (Win XP Access 2003)

    Yes. There was an extraneous piece of info way down in the file. After cleaning up the original file, the date stuff works. .

Posting Permissions

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