IF Statements Involving Multiple Fields in Reports

[AccessNovice]

How do you write an IF statement in a report involving multiple fields when
not all of the fields have data in them?

Here are my fields: Mr/Ms, FirstName, MiddleName, LastName, Jr/Sr

I would like the end result to look like: Mr. John Q. Public, Jr.

Sometimes a record doesn't have a middle name, sometimes it doesn't have a
"Jr.", etc. No matter how I try to write the statement, I seem to be leaving
something out; or it returns extra spaces on the line.

 

[ghetto_banjo]

when someone does not have a MiddleName, is that field storing null or
an empty string?

Assuming they store Null...

=[Mr/Ms] & ". " & [FirstName] & " " & iif(Not IsNull([MiddleName]),
[MiddleName] & ". ", "") & [LastName] & iif(Not IsNull([Jr/Sr], "," &
[Jr/Sr] & ". ", "")

 

[AccessNovice]

That field is storing null.

I tried your statement and got the following msg: The expression you
entered has a function containing the wrong number of arguments.

 

[ghetto_banjo]

oops looks like i forgot a closing parenthesis on that second IsNull
Function .



=[Mr/Ms] & ". " & [FirstName] & " " & iif(Not IsNull([MiddleName]),
[MiddleName] & ". ", "") & [LastName] & iif(Not IsNull([Jr/Sr]), "," &
[Jr/Sr] & ". ", "")

 

[AccessNovice]

I tried that; now it's asking me to enter parameter values for both the Mr/Ms
field and Jr/Sr field. If I enter "Mr." and "Jr.", it returns those values
for every record.

You got me on the right track, though. I tried a modification of your
statement which seems to work, but I'm wondering if it's too "messy" and has
too many unnecessary components. What about this:

=IIf(IsNull([Mr/Ms]),"",[Mr/Ms] & " ") & [FirstName] & " " &
IIf(IsNull([MiddleName]),"",[MiddleName] & " ") & [LastName] &
IIf(IsNull([Jr/Sr]),"",", " & [Jr/Sr])

 

[ghetto_banjo]

if it is asking for a parameter, it means the the Field Names are not
matching up. I assumed that your field was called [Mr/Ms], but if it
is actually called [Title] or whatever else, you need to change it
accordingly. Same goes for the names and the jr/sr.

 

[AccessNovice]

You're right...I had an extra character in the field names.

Everything works wonderfully! Thanks so much for your help!

 

[ghetto_banjo]

no problem, glad to help