Troubleshoot Date in Linked Excel file

[Lizz45ie]

I am trying to create a query based on a Excel linked file. I have a date
field in my linked file that I'm using in my query, but when I use it in my
query it appears to be a text field. What can I do to change the format in
EXcel so that it appears as date in Access? Any help is appreciated.

 

[Ken Snell MVP]

Show us example of what you see in query and what you see in EXCEL.

 

[Lizz45ie]

Here is the sample of the date in the Excel:
6/18/07
6/18/07
6/18/07
2/21/08
5/02/08
9/21/08


Here is the criteria that I typed in Access for records greater than are
equal to:

= "5/01/08" (Access put in double quotes) but it should like the example below:

= #05/01/08# is what it should look in Access if it is a date.

 

[Ken Snell MVP]

Did you try typing in >= #05/01/08# as the criterion in the query? Are you
saying that ACCESS changed it to >="5/01/08" instead?

If yes, then add a calculated field to your query:

ConvDate: CDate([NameOfExcelDateField])

Then use this criterion for the ConvDate field:

= #05/01/08#

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Lizz45ie]

I tried using the CDate to convert the InstallActual Field to a date; I got a
data type mismatch error.

Did you try typing in >= #05/01/08# as the criterion in the query? Are you
saying that ACCESS changed it to >="5/01/08" instead?

If yes, then add a calculated field to your query:

ConvDate: CDate([NameOfExcelDateField])

Then use this criterion for the ConvDate field:

= #05/01/08#

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Here is the sample of the date in the Excel:
6/18/07
6/18/07
6/18/07
2/21/08
5/02/08
9/21/08


Here is the criteria that I typed in Access for records greater than are
equal to:

 

[Lizz45ie]

CDate function worked. Thank you very much.

I tried using the CDate to convert the InstallActual Field to a date; I got a
data type mismatch error.

Did you try typing in >= #05/01/08# as the criterion in the query? Are you
saying that ACCESS changed it to >="5/01/08" instead?

If yes, then add a calculated field to your query:

ConvDate: CDate([NameOfExcelDateField])

Then use this criterion for the ConvDate field:

= #05/01/08#

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Here is the sample of the date in the Excel:
6/18/07
6/18/07
6/18/07
2/21/08
5/02/08
9/21/08


Here is the criteria that I typed in Access for records greater than are
equal to:
= "5/01/08" (Access put in double quotes) but it should like the example
below:

= #05/01/08# is what it should look in Access if it is a date.




:

Show us example of what you see in query and what you see in EXCEL.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I am trying to create a query based on a Excel linked file. I have a
date
field in my linked file that I'm using in my query, but when I use it
in
my
query it appears to be a text field. What can I do to change the
format
in
EXcel so that it appears as date in Access? Any help is appreciated.