exporting worksheet to access

[Judy Margo]

Hello:

I'm looking for help with a specific problem in exporting an Excel
table into Access. I was able to do this successfully (for the most
part), but hit one major glitch. One of the columns in the original
table contains dates. When that column landed in Access, the dates
were transformed into numbers that have no apparent relation to dates
(i.e. simply going in and changing the format of the column to
"Date/Time" didn't help). Instead of being a date w/in the past 10
years or so, there are numbers such as 38945 and 36859. I went back to
Excel and changed the format of the column to perfectly match the
format that I wanted it to be in Access, but this didn't help.

Any ideas?

Thanks very much-
Judy

 

[Guest]

You're looking at serial numbers in Excel, which is how XL stores dates.
1/1/1900 = 0, etc.

From your description it sounds like the dates in your XL workbook are
formatted as text, and, when Access imports data formatted as text, it
retains that text format.

Try formatting the data in Excel as explicitly as possible. Dates should be
formatted as dates, time should be formatted as time, currencies should be
formatted as currencies, accounting transactions should be formatted as
accounting transactions, etc.

Just because you think data is formatted in the correct way, and just
because your eye/experience tell you that 10/1/2006 is October 1st, 2006,
does not mean that the computer thinks the same thing.

Dave

 

[David Biddulph]

Changing the *format* of a number won't change the number itself, so if you
export it to Access you'll still have the same number. If you want it to
look like a date by the time it gets to Access, try a helper column with a
formula such as =TEXT(A1,"dd/mm/yyyy") or whatever format you are looking
for.