Exporting pre-1900 dates to Access?

[Guest]

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!

 

[Guest]

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

 

[Myrna Larson]

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.