If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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. |
#2
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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] & ". ", "") |
#3
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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" wrote: 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] & ". ", "") . |
#4
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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] & ". ", "") |
#5
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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" wrote: 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] & ". ", "") . |
#6
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
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. |
#7
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
You're right...I had an extra character in the field names.
Everything works wonderfully! Thanks so much for your help! "ghetto_banjo" wrote: 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. . |
#8
|
|||
|
|||
IF Statements Involving Multiple Fields in Reports
no problem, glad to help
|
Thread Tools | |
Display Modes | |
|
|