Dates prior to 1900?

[Jim J.]

I am working on a database regarding some very old court records - some of
them are from the mid- to late-1800's.

When I move my data from an Excel spreadsheet (about 15k records), Access
seems to erase the data present in the "published date" field and replaces it
with a null value. It does, however, preserve certain data such as the
barcode number, the publisher name, etc.

Does Access recognize pre-1900 as a date?

 

[Wayne Morgan]

Yes, in fact its Base Date is 30 December 1899. Dates prior to this are
stored as negative numbers. What is the data type of the field you're
importing into and the data type of the cells in Excel?

If I use the Import Wizard, it imports the date field from Excel as Text and
creates a new table. The dates are correct, I just have to modify the table
design to change the data type of the field to Date.

If I link to the spreadsheet as an external table the linked table shows the
data type of the date field as text. I then used an Append query to place
the data in my local table that had the data type of date for the field and
it worked. The dates I used are 12/12/1856, 1/1/1742, and 2/3/1800.

How are you importing the data?

 

[Jim J.]

I am using the Import Wizard to bring the data over from an Excel sheet. I
have ensured that those colums in Excel are formatted as dates (mm/dd/yyyy)
before I import the data into Access.

Should I design the table before I use the import wizard, make sure the
relevant fields are formatted as "general date," and the import the data into
an existing table? Previously, I was letting the import wizard design the
table.

Thanks!

 

[Wayne Morgan]

When using the import wizard, it wouldn't let me use an existing table. It
insisted on creating a new table, so no, I wouldn't create the table and set
its properties. What data type is the wizard making the field in the Access
table?

 

[Jim J.]

I make sure the appropriate columns are properly formatted as dates in Excel.
When Access builds the table with the import wizard, it automatically
formats those fields as date/time. It does not specify the fields as
"general dates" or short dates" or any kind of "subformat" (pardon the
clumsiness of my phrasing) for that field.

 

[Wayne Morgan]

Ok, I did some more playing with this. I noticed when I got the column wide
enough that Excel would right justify the dates on or after 1/1/1900 and
left justify the dates on or before 12/31/1899. If I converted the column to
text, the dates on or after 1/1/1900 would show their serial date numbers,
the dates on or before 12/31/1899 would still show the date. It appears that
even though these columns are being formatted as dates, Excel is treating
anything before 1900 as text. If you are importing into a date type field,
this is probably the cause of the problem.

One work around would be to add 100 or 200 years to all the values in the
Excel sheet then subtract that back out once you have the data in Access. I
haven't tried this, but since the dates before 1900 are being treated as
text, this may be hard to do.

Another work around would be to have 3 fields, one for day, one for month,
and one for year. Recombine them once you have the data in Access.

I tried changing the column in Excel to Text. When I did this, the dates
after 1900 changed to serial dates as mentioned above. I then imported this
into an Access table. Access assigned this column to a text field. I then
created a query using this table and used the following calculated field to
convert the values. It showed all of them correctly.

Test: Format(CDate([Feild1]),"Short Date")

I tried exporting the sheet as a .csv file (comma delimited file) and then
import that into Access. That appeared to work properly. It also didn't gray
out the options to import into a current table or to define the field type
as working directly with the Excel file did. This may be the easiest way to
handle this.

Since the problem appears to be on the Excel side, the Excel folks may be
able to help more.

 

[Jim J.]

Wow. That gives me a lot to work with. Thank you very much! I'll post here
later and let you know how it turns out.

Very much appreciated.

Ok, I did some more playing with this. I noticed when I got the column wide
enough that Excel would right justify the dates on or after 1/1/1900 and
left justify the dates on or before 12/31/1899. If I converted the column to
text, the dates on or after 1/1/1900 would show their serial date numbers,
the dates on or before 12/31/1899 would still show the date. It appears that
even though these columns are being formatted as dates, Excel is treating
anything before 1900 as text. If you are importing into a date type field,
this is probably the cause of the problem.

One work around would be to add 100 or 200 years to all the values in the
Excel sheet then subtract that back out once you have the data in Access. I
haven't tried this, but since the dates before 1900 are being treated as
text, this may be hard to do.

Another work around would be to have 3 fields, one for day, one for month,
and one for year. Recombine them once you have the data in Access.

I tried changing the column in Excel to Text. When I did this, the dates
after 1900 changed to serial dates as mentioned above. I then imported this
into an Access table. Access assigned this column to a text field. I then
created a query using this table and used the following calculated field to
convert the values. It showed all of them correctly.

Test: Format(CDate([Feild1]),"Short Date")

I tried exporting the sheet as a .csv file (comma delimited file) and then
import that into Access. That appeared to work properly. It also didn't gray
out the options to import into a current table or to define the field type
as working directly with the Excel file did. This may be the easiest way to
handle this.

Since the problem appears to be on the Excel side, the Excel folks may be
able to help more.

--
Wayne Morgan
MS Access MVP

I make sure the appropriate columns are properly formatted as dates in
Excel.
When Access builds the table with the import wizard, it automatically
formats those fields as date/time. It does not specify the fields as
"general dates" or short dates" or any kind of "subformat" (pardon the
clumsiness of my phrasing) for that field.