Importing from Excel (Win XP Access 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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 and post 661,499 helpful?

Reply | Quote

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?

Reply | Quote

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]))

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)

Reply | Quote

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.

Reply | Quote

It would become

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

Reply | Quote

Yes!!!

Many thanks.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote