Iif Statement with Null Values

[JD McLeod]

I have a report on which I have an if statement with two conditions. I need
to know how to write the formula to include a third statement if the field
[Type] is blank. In my form, it is possible for the user to not know the
type of control just yet, so I have given them the option to leave that field
[Type] blank. However, on my report with the formula below, it automatically
defaults to inserting a “Pâ€. How do I write the formula to include so that
if [Type] is blank, it will leave it blank on the report, “ “. Thanks. Here
is the formula I currently have.

=IIf([Type]='Detective',"D","P")

 

[Allen Browne]

You can nest your IIf() functions:
=IIf([Type] Is Null, Null, IIf([Type]='Detective',"D","P"))

 

[John Spencer]

Assuming that Type could be either null or a zero length string I would use
the following expression.

=IIf([Type]="Detective","D",IIF(Len([Type] & "")=0,[Type],"P"))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County