Calculated Field Doesn't Work When Criteria Given 1

[JoyInOK]

The following query to determine which accounts were late in submitting a report works when no criteria is given for the number of days ("Which accounts were more than zero days late?&quot, but prompts for [DueDate] when criteria is given in this field. It's as if the query stops recognizing the expression DueDate.
Table 1: GEN_ED_T
Fields:
FedTaxID 'text field holding tax id number
SubNo1: 'text field holding account number
Table 2: REQ$_T
Fields:
SubNo2: 'text field holding account number
ReqRcvdDate: 'date field holding date report recieved
MONTH: 'Text field holding month of report
Expressions in Query:
DueDate: 'determines the date report was due based on the MONTH field. Reports are due on the 15th of the following month (January's report is due February 15)
LateInDays: ' the number of days late, calculated by subtracting the DueDate from the date received

SELECT GEN_ED_T.FedTaxID, CDate((DateAdd(&quot;m&quot;,+1,CDate([MONTH] & &quot; 15, &quot; & IIf(DatePart(&quot;m&quot;,[ReqRcvdDate])<>1,CStr(DatePart(&quot;yyyy&quot;,[ReqRcvdDate])),CStr(DatePart(&quot;yyyy&quot;,DateAdd(&quot;yyyy&quot;,-1,[ReqRcvdDate])))))))) AS DueDate, [REQ$_T].ReqRcvdDate, [REQ$_T].MONTH, [ReqRcvdDate]-[DueDate] AS LateInDays
FROM GEN_ED_T INNER JOIN [REQ$_T] ON GEN_ED_T.SubNo1 = [REQ$_T].SubNo2
WHERE ((([REQ$_T].ReqRcvdDate) Is Not Null And ([REQ$_T].ReqRcvdDate) Between #7/1/2002# And #6/30/2003#) AND (([REQ$_T].MONTH) Is Not Null));

Any ideas on how to specify that I only want those accounts who actually were late?

 

[lameid]

If you want to use criteria on a calculated field, no piece of it can be the result of another calculated field like DueDate. Two workarounds... Put the entire expression for DueDate in your LateInDays calculation or base a query off your query and use criteria in it. My gut says the former will execute faster, but it has been wrong before.

 

[JoyInOK]

Thanks, Lameid. I had already tried basing a second query off the first, but no dice. Same error of &quot;Invalid use of null&quot; when I put criteria in a calculated field. (I did double-check that there were no null values in the records that could cause this error; I think it's a result of adding criteria.)
I also put all the calcluations in one field, but to no avail.

 

[lameid]

One of the values used in your expression must be null... You need to either use NZ to substitute null values for a default value in your expression, eliminate nulls from being considered or clean up your data.

 

[JoyInOK]

Lameid, have a star~~~~~~~!
I had tried to eliminate the records with nulls within the query, but the expression wanted to evaluate all the records (including those with null values) before deciding which to return in the query results, which produced the error.
An iif statement solved the problem.
LateInDays: IIf(IsNull([ReqRcvdDate]) Or IsNull([MONTH]),0,[ReqRcvdDate]-nz((CDate((DateAdd(&quot;m&quot;,+1,CDate([MONTH] & &quot; 15, &quot; & IIf(DatePart(&quot;m&quot;,[ReqRcvdDate])<>1,CStr(DatePart(&quot;yyyy&quot;,[ReqRcvdDate])),CStr(DatePart(&quot;yyyy&quot;,DateAdd(&quot;yyyy&quot;,-1,[ReqRcvdDate])))))))))))
Thanks!