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
IIF statement in a report
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF statement in a report
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: IIF statement in a report
Thanks so much! - I will work on these...
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: IIF statement in a report
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.
=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.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF statement in a report
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!)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: IIF statement in a report
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 :-)
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 :-)
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF statement in a report
You can either put frontend and backend in the zip file, or import the tables into the frontend (after removing the linked tables).
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: IIF statement in a report
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: IIF statement in a report
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 96
- Joined: 11 Feb 2010, 21:59
- Location: Ohio
Re: IIF statement in a report
Thank you so much!
I always learn so much from you...
I always learn so much from you...
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands