Import from Excel - Subscript out of range

[Tony Williams]

I am importing a spread sheet from Excel and keep getting a Subscript out of
range message. I've narrowed the problem down to an Excel row that holds a
date - if I don't import that row the import works. The date in the
spreadsheet is March 2009 but when I import it it shows as 39873. I've played
around with different formats but still get the error. Can anyone help?
Thanks
Tony

 

[Jerry Whittle]

It's probably trying to import into an Integer field which runs out at about
32,000.

Make sure that the table which you are importing into has the field set up
as a Date/Time data type or at least a Long Integer data type. If it's in a
Long Integer, it won't look like a date; however, it could be made one with
the CDate function.

CDate(39873) = 3/1/2009

 

[Tony Williams]

I appear to have solved the problem. I made sure the format in Access and
Excel were both mmmm yyyy so that I could get say March 2009 and it worked
fine.
Thanks for your tip though.
Tony