Date Criteria Problem

[stumped]

I am working with some data that has dates stored as text so I use the CDate function to change them It works fine.
I now need to do some tests on them, so I do [ClosDate] - [RecDate] and it gives me the right number. I do a vartype and its comes back as a double.
I now need to filter for anything >28, so i enter that on the Criteria line and when i run the query I get asked for parameter values for ClosDate and Recdate.
Anyone any idea what i am doing wrong please.

PS I did try DateDiff and I think I got a data type miss match error when I entered the criteria.

Any help much appreciated - this is taking too long!!

[JoeM]

Can you post the SQL code of your query?

[stumped]

Hertis!
SELECT dbo_CallLog.CallID, dbo_CallLog.CallType, dbo_CallLog.CallStatus, dbo_CallLog.Priority, dbo_CallLog.ClosedDate, dbo_CallLog.RecvdDate, CDate([RecvdDate]) AS RecDate, CDate([ClosedDate]) AS ClosDate, ([ClosDate]-[RecDate]) AS Expr1, VarType([expr1]) AS Expr2
FROM dbo_CallLog;

[stumped]

and with the criteria
SELECT dbo_CallLog.CallID, dbo_CallLog.CallType, dbo_CallLog.CallStatus, dbo_CallLog.Priority, dbo_CallLog.ClosedDate, dbo_CallLog.RecvdDate, CDate([RecvdDate]) AS RecDate, CDate([ClosedDate]) AS ClosDate, ([ClosDate]-[RecDate]) AS Expr1, VarType([expr1]) AS Expr2
FROM dbo_CallLog
WHERE (((([ClosDate]-[RecDate]))>28));

[JoeM]

Give this a try:

SELECT
    dbo_CallLog.CallID, 
    dbo_CallLog.CallType, 
    dbo_CallLog.CallStatus, 
    dbo_CallLog.Priority, 
    dbo_CallLog.ClosedDate, 
    dbo_CallLog.RecvdDate,   
    CDate([RecvdDate])AS RecDate, 
    CDate([ClosedDate])AS ClosDate
FROM dbo_CallLog
WHERE (CDate([ClosDate])-CDate([RecDate]))>28;

[stumped]

Still get the parameter value prompt - followed by some other stuff about the expression being typed incorrectly or the query being too complex! (I did copy and paste it)

[JoeM]

Oops, I see the problem,and I missed it initially. You cannot use calculated fields in your criteria like that. Use the calculation itself, i.e.

SELECT
    dbo_CallLog.CallID, 
    dbo_CallLog.CallType, 
    dbo_CallLog.CallStatus, 
    dbo_CallLog.Priority, 
    dbo_CallLog.ClosedDate, 
    dbo_CallLog.RecvdDate, 
    CDate([RecvdDate])AS RecDate, 
    CDate([ClosedDate])AS ClosDate
FROM dbo_CallLog
WHERE (CDate([ClosedDate])- CDate([RecvdDate]))>28;

[stumped]

Thanks for your help but still getting the error "Date type missmatch in crieria expression"

[JoeM]

If you run the query without the criteria, does it work?
If so, take a look at your CDate conversions. Do you have any errors or blank/null values?
If so, those will obviously mess up the criteria calculation, and need to be accounted for.

[stumped]

Point 1, yes it runs correctly.
Points 2 and 3 - haven't checked yet but I think you have it - I recall that I had to test extensively for blank/null values on this dataset when I wrote a function that involved dates earlier. Thanks, I should have though of that!!

[JoeM]

To handle the Nulls, you can use the NZ function to replace Nulls with some default value,
and/or
you can use "Is Not Null" in the criteria fields to weed those out.