Query Criteria - limiting max value of output

[Southsider]

I have a field that calculates the difference between two dates (formula below). It is outputting in numeric format (I looked at the table in design view). Whenever I add a criteria of <365 (to help eliminate bad data) the whole query returns a table with no information. Without any criteria in the field it works normally, but I am seeing some results of 4000 days difference and it's messing up my averages so I need to put a realistic cap on the output of this field.

Here's the formula used in this field:

Days App to UW Receipt: IIf([CDF90 DATE- CREDIT RECEIVED BY UW]-[B_Inter_Date]<0,Null,[CDF90 DATE- CREDIT RECEIVED BY UW]-[B_Inter_Date])

Thanks for any help!

 

[jonathanchye]

Instead of the NULL in the expression try using 0 (zero).

 

[Southsider]

Instead of the NULL in the expression try using 0 (zero).

Thanks for the suggestion. The problem with that is that there are legitimate results of 0 days that I need to take into consideration once I build a report from this data in Excel, so that would skew the results..

 

[jonathanchye]

In that case try changing query criteria to : Is Not Null AND <=365

 

[Southsider]

In that case try changing query criteria to : Is Not Null AND <=365

Hmm that didn't work either. Returned 0 rows. Thanks anyway.

 

[jonathanchye]

This is most probably because some of the results are invalid (null) hence cannot be evaluated against your criteria.

One thing to try is to add NZ on each field.

Days App to UW Receipt: IIf(Nz([CDF90 DATE- CREDIT RECEIVED BY UW],0)-Nz([B_Inter_Date],0)<0,Null,Nz([CDF90 DATE- CREDIT RECEIVED BY UW],0)-Nz([B_Inter_Date],0))

use Is Not Null as criteria

However I would think the best solution is to add 2 columns for those 2 fields in the query designer and use "WHERE" with criteria "Is Not Null"

 

[Southsider]

That worked, thanks a lot!

This is most probably because some of the results are invalid (null) hence cannot be evaluated against your criteria.

One thing to try is to add NZ on each field.

Days App to UW Receipt: IIf(Nz([CDF90 DATE- CREDIT RECEIVED BY UW],0)-Nz([B_Inter_Date],0)<0,Null,Nz([CDF90 DATE- CREDIT RECEIVED BY UW],0)-Nz([B_Inter_Date],0))

use Is Not Null as criteria

However I would think the best solution is to add 2 columns for those 2 fields in the query designer and use "WHERE" with criteria "Is Not Null"

 

[jonathanchye]

Glad I could be of assistance

 

[Southsider]

OK, I have another field that I am having a similar issue with and I can't seem to get the same solution to work. Here's the formula:

Days Recvd to 1st Decision: IIf(DateDiff("d",[CDF90 DATE- CREDIT RECEIVED BY UW],[First UW Result Date])<0,Null,DateDiff("d",[CDF90 DATE- CREDIT RECEIVED BY UW],[First UW Result Date]))

I'm trying to limit the maximum returned value to 365 or less on this as well. Any help would be greatly appreciated.