IIF statement in a report

[Spider]

Hi all,
I have a problem with a report that I've been working on (off and on)
for several days and cannot get an IIF statement to work correctly on a
report in two areas.

The report is a letter to physicians that has multiple laboratory
values; string and numeric.

I need "NA" (not applicable) to print in place of some fields on a
report for three different scenarios...Null, 0 (number zero) and an
#ERROR field.

I'm using an IIF that is working fine for the Null scenario:
=IIf(IsNull([fieldName]),"NA")

But if the result is a 0 (zero), the NA prints on top of the zero -so
that it is not readable. Can the zero be hidden as the NA prints? I've
tried several variations =IIF([fieldName]=0, "NA"

Lastly, one of the fields is from a calculation in the underlying query
(BMI calculated from ht and wt) if that field is null, I get the #ERROR
- is there a way to hide that error and replace with NA?

Thanks,
Vicky

[HansV]

I'd remove the text boxes directly bound to the fields, and replace them with text boxes with formulas like this:

For a number field:

=IIf(Nz([FieldName],0)=0,"NA",[FieldName])

For a text field:

=IIf(Nz([FieldName],"")="","NA",[FieldName])

For the BMI, you can perhaps use something like this:

=IIf(IsNull([ht]),"NA",[BMI])

Make sure that these text boxes don't have the same name as any field in the record source of the report - that would cause confusion.

[Spider]

Thanks so much! - I will work on these...

[Spider]

Everything is working beautifully except the:

=IIf(IsNull([ht]),"NA",[BMI])

It did get rid of the #ERROR (which did make me happy)-but now it displays #NAME.
Any ideas that I might try?
I tried ht 'And' wt and several other variations.

[HansV]

Could you provide detailed information about the query and report, or attach a strippped down, compacted and zipped copy of the database? (Please remove sensitive information!)

[Spider]

Will do...
It's a split database, should I put it back together before sending?
I've been working on putting it together and stripping a lot out a-nd made quite a mess so it may take me a bit :-)

[HansV]

You can either put frontend and backend in the zip file, or import the tables into the frontend (after removing the linked tables).

[Spider]

Here it is...
The main query is 'qryPatientLetter'
The report is 'rptPatientLetter'
The problematic fields on the report are 'PREBMI' & 'POSTBMI' that is listed in the first row of Risk Factors called 'Obesity / Overfat'
Thanks so much for looking at this!
And - no hurry - next week would be fine if you are busy.
Vicky

[HansV]

I'd change the definitions of PREBMI and POSTBMI in the query:

PREBMI: IIf([PREHt]=0,Null,[PREWt]/([PREHt]*[PREHt])*703)
POSTBMI: IIf([POSTHt]=0,Null,[POSTWt]/([POSTHt]*[POSTHt])*703)

This will make these fields return Null if height is 0.

You can then change the control source for the text boxes on the report to

=Nz([PREBMI],"NA")
=Nz([POSTBMI],"NA")

You MUST change the name of the second text box to something different from POSTBMI, for example txtPOSTBMI, otherwise Access becomes confused.

[Spider]

Thank you so much!
I always learn so much from you...

[HansV]

You're welcome. Have a good weekend!