complicated IIF

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

I have an unbound field on a report with this control source:

=IIf([ParentalStress]<14,"0",IIf([ParentalStress]>19,"2","1"))

The field is in a query that does a lot of calculation already. I want to
display 0, 1, or 2 in the report depending on the result of the calculation
in the query.

Here's the problem, [ParentalStress] can be null in the query. If so, the
report shows "1" instead of "0". Is there a way to fix this?
 
Hi Julie

Karl Dewey wrote:

Try this --
=IIf(Nz([ParentalStress],0)<14,"0",IIf([ParentalStress]>19,"2","1"))

As a matter for explanation, the Nz function changes a Null value to a
specified value (in this case 0) and will work to achieve the result you
want. However, there is also a technical flaw in the logic you have used.

If you analyse the two IF statements, the Null tests FALSE for the first
test and then FALSE for the second test giving you the result of 1.

The logic can be changed to trap the Null and then combined with the Nz
function.

Assuming that [ParentalStress] is an integer, the logic could be changed to
trap the Null and display 0 simply because the Null does not meet any of the
other conditions.

=IIf([ParentalStress]>19,"2",IIf([ParentalStress]>14,"1","0")

Since Null is NOT > 19 it tests against the second logical test where Null
is NOT > 14 and so gives a result of 0.

=IIf([ParentalStress]>19,"2",IIf(Nz([ParentalStress])>14,"1","0")

Cheers

Stephen @ ZennHAUS
Julie said:
I have an unbound field on a report with this control source:

=IIf([ParentalStress]<14,"0",IIf([ParentalStress]>19,"2","1"))

The field is in a query that does a lot of calculation already. I want
to
display 0, 1, or 2 in the report depending on the result of the
calculation
in the query.

Here's the problem, [ParentalStress] can be null in the query. If so,
the
report shows "1" instead of "0". Is there a way to fix this?
Click to expand...
Click to expand...
 
I would hesitate about hard-coding the values 14 and 19 into an expression.
This seems like a scale/rating that could change in the future (even if you
think it won't). There is almost always some pointy-haired boss who wants the
values to be 12 and 18 or whatever.

At the very least, I would create a small user-defined function in a module
of business rules (modBusinessCalcs) that would accept the ParentalStress
values and return an integer (not a string) of 0, 1, 2, or possibly new
values.

Ideally you would have a small lookup table of ranges. You should be
maintaining data, not expressions or code.
--
Duane Hookom
Microsoft Access MVP


Stephen @ ZennHAUS said:
Hi Julie

Karl Dewey wrote:

Try this --
=IIf(Nz([ParentalStress],0)<14,"0",IIf([ParentalStress]>19,"2","1"))

As a matter for explanation, the Nz function changes a Null value to a
specified value (in this case 0) and will work to achieve the result you
want. However, there is also a technical flaw in the logic you have used.

If you analyse the two IF statements, the Null tests FALSE for the first
test and then FALSE for the second test giving you the result of 1.

The logic can be changed to trap the Null and then combined with the Nz
function.

Assuming that [ParentalStress] is an integer, the logic could be changed to
trap the Null and display 0 simply because the Null does not meet any of the
other conditions.

=IIf([ParentalStress]>19,"2",IIf([ParentalStress]>14,"1","0")

Since Null is NOT > 19 it tests against the second logical test where Null
is NOT > 14 and so gives a result of 0.

=IIf([ParentalStress]>19,"2",IIf(Nz([ParentalStress])>14,"1","0")

Cheers

Stephen @ ZennHAUS
Julie said:
I have an unbound field on a report with this control source:

=IIf([ParentalStress]<14,"0",IIf([ParentalStress]>19,"2","1"))

The field is in a query that does a lot of calculation already. I want
to
display 0, 1, or 2 in the report depending on the result of the
calculation
in the query.

Here's the problem, [ParentalStress] can be null in the query. If so,
the
report shows "1" instead of "0". Is there a way to fix this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top