Date fields break GROUP By or ORDER By clauses

[colin_e]

I'm beginning to question my own sanity here.

I have a simple query that derives these fields:

UID Firstname Lastname Registered Updated Accessed Num

The fields Registered Updated Accessed are strings in the underlying tables,
but converted to Date using DateValue() in the Query (no errors).

I then wanted to create a simple report on the lifespan (time between
registered date and accessed date). The function DateDiff("ww",
a.registered, a.accessed) calculates the lifespan of the account in weeks.
This works ok in a SELECT.

However the following query fails:

SELECT *
FROM Accounts_Accessed AS a
ORDER BY DateDiff("ww", a.registered, a.accessed) DESC;

Fails with the error:

"Data type mismatch in criteria expression". All the values being used here
are Dates, so i'm confused as to what's going on.

The only hint I have is that I started having this trouble after I built the
DateValue() conversion into the intermediate Query. Before that I was
processing everything as strings and then converting to dates only when I
needed to do date calculations, but this was a pain to remember when the
conversions were needed.

Can access really not handle WHERE clauses when types are converted in
intermediate queries?

 

[KARL DEWEY]

The fields Registered Updated Accessed are strings in the underlying tables,
but converted to Date using DateValue() in the Query (no errors).

Post the SQL for the query.