Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Exporting pre-1900 dates to Access?
I have a fairly large (15k items) Excel worksheet that relates to a catalog
of legal texts, some of which were published prior to 1900. The field in Excel is formatted as a date (mm/dd/yyyy). When I use the Access import wizard to bring the data into an Access table, it properly recognizes the field as a date. However, it seems to treat anything prior to 1/1/1900 as an invalid date and inserts a null value into that field. An Access MVP has thought of a few workarounds, but also suggested I post here to see if anyone had a better idea. Many thanks in advance! |
#2
|
|||
|
|||
Ugly 4 column workaround
in Excel add Year,month,day columns rename existing to Display Date have macro use existing cell info to fill in year,month,day and then copy paste as text format into the display field ugly but effective you now can display from access the sameway you had it in Excel and sort by year,month,day "Jim J." wrote: I have a fairly large (15k items) Excel worksheet that relates to a catalog of legal texts, some of which were published prior to 1900. The field in Excel is formatted as a date (mm/dd/yyyy). When I use the Access import wizard to bring the data into an Access table, it properly recognizes the field as a date. However, it seems to treat anything prior to 1/1/1900 as an invalid date and inserts a null value into that field. An Access MVP has thought of a few workarounds, but also suggested I post here to see if anyone had a better idea. Many thanks in advance! |
#3
|
|||
|
|||
In case you are wondering why you are getting the error, it's probably because
in Excel post-12/31/1899 dates are stored as Excel dates (which are numbers, with 1 = Jan 1, 1900, 38614 = Sep 19, 2005). "Dates" of 12/31/1899 and earlier are stored in Excel as text. IOW, you have two different types of data in this column on the spreadsheet. I suppose when you import the data into Access, the data type is inferred from the first row, and that happens to be a true Excel data (numeric). So the field type is set up as date. When you later encounter a pre-1/1/1900 "date", it's text rather than a number, and Access "says" this column has to be numeric, text is illegal. Rather than trying to translate the text to a number, Access fills that row with a null. As far as a "better idea" is concerned, you might change ALL of the dates in the spreadsheet to text in the format yyyy-mm-dd. If the dates are in, say, column B, with a header in row 1, insert a new column C, and in C2 put this formula and copy it down: =TEXT(B2,"yyyy/mm/dd") Then select all of the formulas, Edit/Copy, and without changing the selection Edit/Paste Special and select the Values option. Then you can delete the original column B. See if, when you import that file into Access, the dates now come in as dates or as text. If it's text, maybe that's OK. With this format you can still sort the dates correctly. If you want to have true dates, you could insert a new date field in the Access table, when use an Update query to update that column. Let's say you name the original field PubDateAsText, and the new date field as PubDate. In the update query, you would update the PubDate field, and in the Update To box, type DateValue([PubDateAsText]). If you won't be updating the database with new records, you could then delete the PubDateAsText field. On Mon, 19 Sep 2005 07:02:06 -0700, Jim J. wrote: I have a fairly large (15k items) Excel worksheet that relates to a catalog of legal texts, some of which were published prior to 1900. The field in Excel is formatted as a date (mm/dd/yyyy). When I use the Access import wizard to bring the data into an Access table, it properly recognizes the field as a date. However, it seems to treat anything prior to 1/1/1900 as an invalid date and inserts a null value into that field. An Access MVP has thought of a few workarounds, but also suggested I post here to see if anyone had a better idea. Many thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting fields from access to excel | Excel Discussion (Misc queries) | |||
Dates earlier than 1900 | Excel Discussion (Misc queries) | |||
How can I get Excel 2003 to display dates before 1900 | Excel Discussion (Misc queries) | |||
Error message in Excel after exporting Access query to Excel | Excel Discussion (Misc queries) | |||
Need to sort dates before 1900 in proper order | Excel Discussion (Misc queries) |