Date Field Type Mismatch with Query Criteria 1

[calandrelli]

When I run the following query everything worls fine...

SELECT qry_Queue_Closed.PCKG_NBR, qry_Queue_Closed.PCKG_INITIATION_DT, qry_Queue_Closed.PCKG_APPROVED_DT, qry_Queue_Closed.OP_TYP_DESC, [PCKG_INITIATION_DT]+30 AS [Due Date], CDate(DateAdd("w",(-1*(DatePart("w",[PCKG_CLOSED_DT])-2)),[PCKG_CLOSED_DT])) AS [Week Of], qry_Queue_Closed.PCKG_STS_DESC, IIf(DateValue([PCKG_CLOSED_DT])>DateValue([Due Date]),1,0) AS Late, qry_Queue_Closed.LOCAL_AREA_DESC
FROM qry_Queue_Closed
WHERE (((qry_Queue_Closed.PCKG_CLOSED_DT) Is Not Null));

But when I add another "Where Clause" criteria for the calculated date field "Week Of" I get a type mismatch error....

SELECT qry_Queue_Closed.PCKG_NBR, qry_Queue_Closed.PCKG_INITIATION_DT, qry_Queue_Closed.PCKG_APPROVED_DT, qry_Queue_Closed.OP_TYP_DESC, [PCKG_INITIATION_DT]+30 AS [Due Date], CDate(DateAdd("w",(-1*(DatePart("w",[PCKG_CLOSED_DT])-2)),[PCKG_CLOSED_DT])) AS [Week Of], qry_Queue_Closed.PCKG_STS_DESC, IIf(DateValue([PCKG_CLOSED_DT])>DateValue([Due Date]),1,0) AS Late, qry_Queue_Closed.LOCAL_AREA_DESC
FROM qry_Queue_Closed
WHERE (((CDate(DateAdd("w",(-1*(DatePart("w",[PCKG_CLOSED_DT])-2)),[PCKG_CLOSED_DT])))=#5/2/2005#) AND ((qry_Queue_Closed.PCKG_CLOSED_DT) Is Not Null));


Any Ideas?????

 

[lespaul]

why don't you explain what you are trying to accomplish with :

(((CDate(DateAdd("w",(-1*(DatePart("w",[PCKG_CLOSED_DT])-2)),[PCKG_CLOSED_DT])))=#5/2/2005#)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

DateAdd returns a Date, so CDate is not needed.
you may have to play with the Nz function in the where clause in case the DatePart condition is evaluated before the Is not Null condition.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[calandrelli]

Leslie, I am using the date 5/2/05 just as an example.

The field [Week Of] is being calculated to take a date of a transaction from another table and assign the "Week Of" date as a new field. For example, if the transaction happened on 8/10/05 the Week of field would be the Monday of that week or the week of 8/8/05. I then want to replace the 5/2/05 with

= >Date()-84 and <=Date

this would get me 12 weeks of data from the much larger set I am pulling in.


The part I don't understand is that the "PCKG_CLOSED_DT" field is a Date Time Field. The DateAdd and DatePart Functions work perfectly with it and the CDate returns a value in Date/Short format, but I can't use a date as a criteria.

I have even tried creating another query using this one as the source and the same thing happens.

If I change it to a make table and then query that table with the 5/2/05 critera...it works fine.

Why doesn't it work in the query?

 

[calandrelli]

PHV gets a star!!! I didn't have to mess with the NZ value but it got me thinking in the right direction. An order can not have a PCKG_CLOSED_DT if it is not Closed so I got rid of the Null statment and added the following...

WHERE (((CDate(DateAdd("w",(-1*(DatePart("w",[PCKG_CLOSED_DT])-2)),[PCKG_CLOSED_DT])))>#5/2/2005#) AND ((qry_Queue_Closed.PCKG_STS_DESC)="Closed"));

This eliminated the null values and solved the problem!

THANKS!