Troubleshoot Date in Linked Excel file

  • Thread starter Thread starter Lizz45ie
  • Start date Start date
L

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.
 
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:
Click to expand...
= #05/01/08# is what it should look in Access if it is a date.
Click to expand...
 
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#
Click to expand...


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
 
I tried using the CDate to convert the InstallActual Field to a date; I got a
data type mismatch error.

Ken Snell MVP said:
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#
Click to expand...


--

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




Lizz45ie said:
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:
Click to expand...
Click to expand...
 
CDate function worked. Thank you very much.

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

Ken Snell MVP said:
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#
Click to expand...


--

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




Lizz45ie said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top