Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
exporting worksheet to access
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
exporting worksheet to access
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 -- Brevity is the soul of wit. "Judy Margo" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
exporting worksheet to access
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. -- David Biddulph "Judy Margo" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I restrict access to only one worksheet in excel? | Excel Discussion (Misc queries) | |||
Exporting an Excel Chart into Access | Charts and Charting in Excel | |||
I copied a workbook to a CD. I can only access 1 worksheet. | Excel Discussion (Misc queries) | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
exporting excel worksheet to word | Links and Linking in Excel |