#NUM! in Field from Linked Excel Spreadsheet

[Guest]

I link to a spreadsheet that has three date fields....two link OK and one
shows up as #NUM! in the datasheet when I look. I noticed in the design that
the date field shows up as text (the other two show up properly as
date/time), but the Excel spreadsheet format for that cell is date.

Why is Access seeing that field as text? Is there something I'm missing in
that cell in Excel...hey, it looks like the other two.

I'm totally flummoxed. Help!

 

[Roger Carlson]

When you link a spreadsheet from Excel into Access, it determines the
datatype based on the first few lines in the file. (about 10, I think). If
there is no data, it decides it's Text. I have never found a way to correct
it other than to make sure the correct datatype appears in the first few
records.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Nurick]

Hi Roger,

See these for more than you ever wanted to know!
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
http://support.microsoft.com/?id=257819

 

[Guest]

Thanks. Interesting reading. I had the originator of the Spreadsheets
change the properties of their date from text to date/time and won't be
needing last year's data (I hope). Next time that happens to me, I won't be
tearing my hair out.