qry FORMULA ISSUE

W

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.
 
R

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
 
W

William

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


Rob Parker said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top