qry FORMULA ISSUE

[William]

I have a query with the following formula:

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

In one of the records, these are the values:

Yescount: 67
ETcount: 65
ITcount: 0
LPcount: 0

So the formula SHOULD calculate:

IIf((67-0-0)>0,65*100/(67-0-0),0)

Since 67 is greater than 0, I expect the formula to calcuate:
6500/67 = 97.015

But the formula returns 0.00 in this case.

One possible hint is that the zeros in the IT and LP fields are "forced" to
appear by using the nz function. When there are numbers in those fields
greater than zero, the formula does calculate correctly.

Thanks for ideas.

 

[Rob Parker]

If this expression is in a query based on a query which uses the Nz function
to return the ITcount and LP count values then I would expect it to work
correctly. However, if the expression is in the same query, you cannot use
the modified values of ITcount and LPcount, but must use the Nz expression
in this calculated field also; it would be:
IIf(([YEScount]-Nz([ITcount],0)-NZ([LPcount],0))>0,[ETcount]*100/([YEScount]-Nz([ITcount],0)-Nz([LPcount],0)),0)

HTH,

Rob

 

[William]

Thanks, Rob Parker's formula worked perfectly.
--
William

If this expression is in a query based on a query which uses the Nz function
to return the ITcount and LP count values then I would expect it to work
correctly. However, if the expression is in the same query, you cannot use
the modified values of ITcount and LPcount, but must use the Nz expression
in this calculated field also; it would be:
IIf(([YEScount]-Nz([ITcount],0)-NZ([LPcount],0))>0,[ETcount]*100/([YEScount]-Nz([ITcount],0)-Nz([LPcount],0)),0)

HTH,

Rob

I have a query with the following formula:

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

In one of the records, these are the values:

Yescount: 67
ETcount: 65
ITcount: 0
LPcount: 0

So the formula SHOULD calculate:

IIf((67-0-0)>0,65*100/(67-0-0),0)

Since 67 is greater than 0, I expect the formula to calcuate:
6500/67 = 97.015

But the formula returns 0.00 in this case.

One possible hint is that the zeros in the IT and LP fields are
"forced" to appear by using the nz function. When there are numbers
in those fields greater than zero, the formula does calculate
correctly.

Thanks for ideas.