Linked Excel table Data Type

[sooshi]

Hello all,

I was hoping someone with some better knowledge than I could help.

I have a linked table in an Access Database, linked to an Excel Spreadsheet.

I have a field within this spreadsheet that holds date/time data in a custom format (dd/mm hh:mm). However access has the data type set to "text" meaning that when I pull data out of the table, the field in question come back as "#Num!".

I have other fields in the source spreadsheet in the same custom date/time format, and Access has treated those fields as date/time.

I know I can't change the data type of a field in a linked table in access itself, so I'm guessing the key lies with the source excel spreadsheet, but I just can't get it to change. I've tried copying the format from the other wokring date/time field into the dodgy one, but to no avail.

Does *anyone* know how to resolve this? It's causing real problems with my queries.

Thanks in advance guys!

 

[DCrake]

There may be at least one cell that causes conflict with the rest. Post a copy of the spreadsheet if possible to test.

David

 

[Dennisk]

Access cannot import dates as text without the day i.e 01/02/2009 is valid but 02/2009 is not. Access always expects the full date.

You could import into a text field add the day to the date then append to the data table.

 

[sooshi]

Thanks for the offer of help

but for some reason, the problem has spontaneously *touch wood* fixed itself.

Well, that's obviously not true.. but i did something that sorted it.. though I'm still not sure what.

In any case, thanks again

 

[David Eagar]

Does your EXCEL sheet have headings? Access will read the first row and format according to what it finds