Omit nickname in concatenated field

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a database where [NickName] is used as the means of addressing
a member if the member is not commonly known by his formal
[FirstName].

In a report I need to include the [NickName] if it is different from
the [FirstName] and enclose it in curved brackets. I have used this
code and failed. It reads the " Iif ... Is Null" and omits the
brackets but still quotes the [NickName] when it is the same as the
[FirstName]

="Name: " & [FirstName] & IIf([NickName] Is Null Or
[Nickname]=[FirstName]," "," (" & [Nickname] & ")") & " " & [LastName]

Thanks,

Robin Chapple
 
i tested your expression with the following records:

FirstName NickName LastName
John John Doe
John Doe
John Jay Doe

and got the following return:

John Doe
John Doe
John (Jay) Doe

the first two rows have two spaces between the first and last names. is that
what you're talking about - "omits the brackets but still quotes the
[NickName] when it is the same as the [FirstName]" ?

if so, try

="Name: " & [FirstName] & IIf([NickName] Is Null Or [Nickname]=[FirstName],"
"," (" & [Nickname] & ") ") & [LastName]

if you mean something else, please post same data and return strings (as i
did) to illustrate the problem.

hth
 
Robin said:
In a report I need to include the [NickName] if it is different from
the [FirstName] and enclose it in curved brackets. I have used this
code and failed. It reads the " Iif ... Is Null" and omits the
brackets but still quotes the [NickName] when it is the same as the
[FirstName]

="Name: " & [FirstName] & IIf([NickName] Is Null Or
[Nickname]=[FirstName]," "," (" & [Nickname] & ")") & " " & [LastName]
Click to expand...

That looks good to me. In what way did the expression fail?

It would display #Error if the text box is named the same as
one of the fields in the expression. If that's the case,
just change the name of the text box toosomething like
txtFullName.
 
i tested your expression with the following records:

FirstName NickName LastName
John John Doe
John Doe
John Jay Doe

and got the following return:

John Doe
John Doe
John (Jay) Doe

the first two rows have two spaces between the first and last names. is that
what you're talking about - "omits the brackets but still quotes the
[NickName] when it is the same as the [FirstName]" ?

if so, try

="Name: " & [FirstName] & IIf([NickName] Is Null Or [Nickname]=[FirstName],"
"," (" & [Nickname] & ") ") & [LastName]

if you mean something else, please post same data and return strings (as i
did) to illustrate the problem.

hth


Robin Chapple said:
I have a database where [NickName] is used as the means of addressing
a member if the member is not commonly known by his formal
[FirstName].

In a report I need to include the [NickName] if it is different from
the [FirstName] and enclose it in curved brackets. I have used this
code and failed. It reads the " Iif ... Is Null" and omits the
brackets but still quotes the [NickName] when it is the same as the
[FirstName]

="Name: " & [FirstName] & IIf([NickName] Is Null Or
[Nickname]=[FirstName]," "," (" & [Nickname] & ")") & " " & [LastName]

Thanks,

Robin Chapple
Click to expand...
Click to expand...
Thanks very much.

I had not realised that the report had this in two places. I was
checking only one. The code does work and it is now in both places.

My apology,

Robin Chapple
 
Back
Top