Linked dates

[honey2wood]

Hi to all.
This might be a simple one but it is causing me a lot of grief!
I have a linked table to a spreadsheet. The spreadsheet has dates in date-time format.
When I make a query of the table in access and want to choose a certain date the time part of the data makes it impossible because the time has to be included before it is recognised.
As this is a linked table I can't change the date format at all.
In the query in a seperate column I have included the following 'DateValue([Order Date])' which gives only a date without time but when I try to sort with this it tells me 'data type mismatch in criteria expression'
Any ideas please.

 

[pbaldy]

Are there records with a Null order date? See if this works:

DateValue(Nz([Order Date],0))

 

[honey2wood]

Thanks for spending the time to reply. No there are no null dates. I have tried this but it still comes up with the same.

It actually displays the correct details but after a second or so the message comes up and all the data not just the dates changes to #Name?.This is very strange
Graham

 

[pbaldy]

Can you post the db (or the spreadsheet)? It sounds like bad data of some sort; if not Null, then something that can't be converted to a date.

 

[vbaInet]

I was thinking the DateValue cannot convert the Null alternative, that is 0. Maybe an IIF() function would be of use?

IIF(IsNull([Order Date]),"", DateValue([Order Date]))

 

[honey2wood]

Wow thanks for the quick reply from you both.
I was about to send the database but thought I would try the IIF statement first and it worked.
I am not really familiar with this IIF but will have to read up about it so that I can use it in future.
Many Thanks

Graham

 

[vbaInet]

You're welcome!

 

[pbaldy]

So there were nulls. This would probably also have worked:

DateValue(Nz([Order Date], #1/1/1900#))

and may be preferable, as you might hit a mismatch error when trying to put a criteria on a field that contains both dates and strings (""). Might not though. Good catch in any case, vbaInet. I was thinking the 0 would be interpreted by the DateValue function, but testing reveals that it isn't.