Linked table field not picking up correct data type from excel

[gerryg]

Hi,

So as the title indicates i have an access application whereby i have a linked table pulling data from an external excel sheet (.xls). One of the columns within the excel sheet holds a date in the format dd/mm/yyyy. The issue is that the linked table seems to be picking up that the data type of this column is "Text", and unfortunately this is a big issue as i need to create querys using this date attribute (i.e. > Date() +1 etc). I am using Access 2007. Any help appreciated.

 

[Joe4]

How is the column formatted in Excel (as Text or Date)?
Are there any blanks in that column in Excel?

 

[gerryg]

How is the column formatted in Excel (as Text or Date)?
Are there any blanks in that column in Excel?

In Excel (2007) its formatted as Date (English(United Kingdom)), and there are no blanks.

 

[Joe4]

Are you regional settings set for the UK (and not the US)?
See: https://support.office.com/en-za/ar...ta-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

If so, can you post what you first ten rows of data look like for this Excel file?

 

[gerryg]

Are you regional settings set for the UK (and not the US)?
See: https://support.office.com/en-za/ar...ta-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

If so, can you post what you first ten rows of data look like for this Excel file?

The regional settings are set to UK on my laptop.

The following are the first 10 records

Date	Batch	Reference	School
11/05/2015	1	1	01 St Aidans NS
12/05/2015	1	1	01 St Aidans NS
13/05/2015	1	1	01 St Aidans NS
14/05/2015	1	1	01 St Aidans NS
15/05/2015	1	1	01 St Aidans NS
11/05/2015	1	2	01 St Aidans NS
12/05/2015	1	2	01 St Aidans NS
13/05/2015	1	2	01 St Aidans NS
14/05/2015	1	2	01 St Aidans NS

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[Joe4]

Let's say that you first entry is in cell A2 in Excel. What does this Excel formula return?
=ISNUMBER(A2)
(change A2 to the cell with your first date in it).

 

[gerryg]

It returns False, also IsText(A2) returns True....

 

[Joe4]

That means that the dates you have in Excel have been entered as Text, and not as Dates, which explains why you are seeing them as Text in Access.

You will need to convert them to dates in Excel first before linking the file.
You can do that by changing the format of the column first, and then doing a "Text to Columns" on that data column, and in the third argument selecting the approproate Date option (specifically, the DMY one), and clicking Finish.

 

[gerryg]

That means that the dates you have in Excel have been entered as Text, and not as Dates, which explains why you are seeing them as Text in Access.

You will need to convert them to dates in Excel first before linking the file.
You can do that by changing the format of the column first, and then doing a "Text to Columns" on that data column, and in the third argument selecting the approproate Date option (specifically, the DMY one), and clicking Finish.

Worked perfectly

Thank you