QRY FORMULA ISSUE

  • Thread starter Thread starter William
  • Start date Start date
W

William

I have a gry which contains the following formula:

IIf(([YEScount]-[ITcount]-[LPcount])>0,[ETcount]*100/([YEScount]-[ITcount]-[LPcount]),0)

The issue is that the ITcount and LPcount fields contain some null values
which cause the formula not to calculate in some records. Two questions:

1. I know the nz function can force zeros into the null value fields, but I
am unable to determine the correct placement of nz in the formula to make
that happen. Suggestions?

2. In the same database is another qry that uses a formula identical to the
one above. However, when this query runs, there are no null values in the
fields and the formula calculates correctly in every row. All fields have
zeros and no null values. Yet, just as in the formula above, no nz is
currently in the formula. So there must be another way to achieve the result
of forcing blank fields to display zero other than using the nz function. I
am wondering what that is?

Thanks for assistance,
 
First item:
IIf((Nz([YEScount],0)-Nz([ITcount],0)- Nz([LPcount],0))>0
,[ETcount]*100/(Nz([YEScount],0)-Nz([ITcount],0)- Nz([LPcount],0),0)

Second item:
There is no option to force blank fields to return zero.

In a table you can assign a default value of zero to a field and make the
field a required field. That will automatically force a zero into a field on
a NEW record; existing records won't change.

You can DISPLAY zero when a field contains null (in a control) by setting the
fourth argument of the format property to "0". The field and the control
still are null values.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I have now figured out how to include the nz function in the formula so that
all rows calculate. However, when I run the qry, the fields that now
function as zero still appear blank. So, I'm still not sure how to do that,
and I still am curious about my question #2 below.

Thanks,
 
If you want the fields in the query that are elsewhere to show zero, you
would use the NZ function there also

Field: Yes_Count: Nz(YesCount,0)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top