IIf

Hello. I have 4 fields: FirstName, NickName, LastName,
OldLastName.

There may not be a NickName and/or an OldLastName.

If there is a NickName, I want to use it rather than the
FirstName. If there is an OldLastName, I want to use it
rather than the LastName.

If Margaret (aka Peggy) Barton marries and changes her
last name to Smith, I want to see Peggy Smith. I was OK
until I added in the OldLastName bit. Thanks, IIf and Nz
confuse me. Here's what I had:

=IIf(IsNull(Forms!Individuals!NickName),Forms!Indi viduals!
FirstName & " " & Forms!Individuals!LastName,Forms!
Individuals!Nickname & " " & Forms!Individuals!LastName)

Is this not working?
Are you getting an error? If so, what?
Is this in a form or a report?


"Stephanie" wrote in message
...
| Hello. I have 4 fields: FirstName, NickName, LastName,
| OldLastName.
|
| There may not be a NickName and/or an OldLastName.
|
| If there is a NickName, I want to use it rather than the
| FirstName. If there is an OldLastName, I want to use it
| rather than the LastName.
|
| If Margaret (aka Peggy) Barton marries and changes her
| last name to Smith, I want to see Peggy Smith. I was OK
| until I added in the OldLastName bit. Thanks, IIf and Nz
| confuse me. Here's what I had:
|
| =IIf(IsNull(Forms!Individuals!NickName),Forms!Indi viduals!
| FirstName & " " & Forms!Individuals!LastName,Forms!
| Individuals!Nickname & " " & Forms!Individuals!LastName)

"Stephanie" wrote in message

Hello. I have 4 fields: FirstName, NickName, LastName,
OldLastName.

There may not be a NickName and/or an OldLastName.

If there is a NickName, I want to use it rather than the
FirstName. If there is an OldLastName, I want to use it
rather than the LastName.

If Margaret (aka Peggy) Barton marries and changes her
last name to Smith, I want to see Peggy Smith. I was OK
until I added in the OldLastName bit.

It seems to me you just contradicted yourself. If Peggy Barton changed
her name to Smith, wouldn't "Smith" be her LastName, and "Barton" be her
OldLastName? If you really want to use Old:LastName in preference to
LastName, as you said above, then wouldn't you be wanting to see "Peggy
Barton", not "Peggy Smith"?

Thanks, IIf and Nz
confuse me. Here's what I had:

=IIf(IsNull(Forms!Individuals!NickName),Forms!Indi viduals!
FirstName & " " & Forms!Individuals!LastName,Forms!
Individuals!Nickname & " " & Forms!Individuals!LastName)

If you really want to use OldLastName, if available, and fall back to
LastName, you might use this expression:

=Nz([NickName], [FirstName]) & " " & Nz([OldLastName], [LastName])

If you want to use LastName, if available, and fall back to OldLastName,
you might use this expression:

=Nz([NickName], [FirstName]) & " " & Nz([LastName], [OldLastName])

I've left off the Forms!Individuals form qualifiers, thinking that maybe
you don't need them. That would depend on where the expression is being
used, though, so feel free to add them back in if you need to.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On Mon, 28 Feb 2005 12:37:14 -0800, "Stephanie"
wrote:

Hello. I have 4 fields: FirstName, NickName, LastName,
OldLastName.

There may not be a NickName and/or an OldLastName.

If there is a NickName, I want to use it rather than the
FirstName. If there is an OldLastName, I want to use it
rather than the LastName.

If Margaret (aka Peggy) Barton marries and changes her
last name to Smith, I want to see Peggy Smith. I was OK
until I added in the OldLastName bit. Thanks, IIf and Nz
confuse me. Here's what I had:

=IIf(IsNull(Forms!Individuals!NickName),Forms!Ind ividuals!
FirstName & " " & Forms!Individuals!LastName,Forms!
Individuals!Nickname & " " & Forms!Individuals!LastName)

I'd suggest using an expression

NZ([NickName], [FirstName]) & " " & Nz([OldLastName], [LastName])

Since the nick/first and old/new last decisions are independent of one
another, you need two decision points; and the NZ() function is
simpler than the IIF.

John W. Vinson[MVP]

My bad.
-----Original Message-----
"Stephanie" wrote
in message

Hello. I have 4 fields: FirstName, NickName, LastName,
OldLastName.

There may not be a NickName and/or an OldLastName.

If there is a NickName, I want to use it rather than
the
FirstName. If there is an OldLastName, I want to use
it
rather than the LastName.

If Margaret (aka Peggy) Barton marries and changes her
last name to Smith, I want to see Peggy Smith. I was
OK
until I added in the OldLastName bit.

It seems to me you just contradicted yourself. If Peggy
Barton changed
her name to Smith, wouldn't "Smith" be her LastName,
and "Barton" be her
OldLastName? If you really want to use Old:LastName in
preference to
LastName, as you said above, then wouldn't you be
wanting to see "Peggy
Barton", not "Peggy Smith"?

Thanks, IIf and Nz
confuse me. Here's what I had:

=IIf(IsNull(Forms!Individuals!NickName),Forms!
Individuals!
FirstName & " " & Forms!Individuals!LastName,Forms!
Individuals!Nickname & " " & Forms!Individuals!
LastName)

If you really want to use OldLastName, if available, and
fall back to
LastName, you might use this expression:

=Nz([NickName], [FirstName]) & " " & Nz
([OldLastName], [LastName])

If you want to use LastName, if available, and fall back
to OldLastName,
you might use this expression:

=Nz([NickName], [FirstName]) & " " & Nz([LastName],
[OldLastName])

I've left off the Forms!Individuals form qualifiers,
thinking that maybe
you don't need them. That would depend on where the
expression is being
used, though, so feel free to add them back in if you
need to.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

It is working, but I wanted to add LastName/OldLastName.
Thanks.

-----Original Message-----
Is this not working?
Are you getting an error? If so, what?
Is this in a form or a report?


"Stephanie" wrote
in message
...
| Hello. I have 4 fields: FirstName, NickName, LastName,
| OldLastName.
|
| There may not be a NickName and/or an OldLastName.
|
| If there is a NickName, I want to use it rather than
the
| FirstName. If there is an OldLastName, I want to use
it
| rather than the LastName.
|
| If Margaret (aka Peggy) Barton marries and changes her
| last name to Smith, I want to see Peggy Smith. I was
OK
| until I added in the OldLastName bit. Thanks, IIf and
Nz
| confuse me. Here's what I had:
|
| =IIf(IsNull(Forms!Individuals!NickName),Forms!
Individuals!
| FirstName & " " & Forms!Individuals!LastName,Forms!
| Individuals!Nickname & " " & Forms!Individuals!
LastName)

.

Thanks.
I tried it but it returned: #Error

-----Original Message-----
On Mon, 28 Feb 2005 12:37:14 -0800, "Stephanie"
wrote:

Hello. I have 4 fields: FirstName, NickName, LastName,
OldLastName.

There may not be a NickName and/or an OldLastName.

If there is a NickName, I want to use it rather than
the
FirstName. If there is an OldLastName, I want to use
it
rather than the LastName.

If Margaret (aka Peggy) Barton marries and changes her
last name to Smith, I want to see Peggy Smith. I was
OK
until I added in the OldLastName bit. Thanks, IIf and
Nz
confuse me. Here's what I had:

=IIf(IsNull(Forms!Individuals!NickName),Forms!
Individuals!
FirstName & " " & Forms!Individuals!LastName,Forms!
Individuals!Nickname & " " & Forms!Individuals!LastName)

I'd suggest using an expression

NZ([NickName], [FirstName]) & " " & Nz([OldLastName],
[LastName])

Since the nick/first and old/new last decisions are
independent of one
another, you need two decision points; and the NZ()
function is
simpler than the IIF.

John W. Vinson[MVP]
.

On Mon, 28 Feb 2005 15:14:33 -0800, "Stephanie"
wrote:

Thanks.
I tried it but it returned: #Error

Please post the actual SQL of the query, and the actual field names in
your table.

John W. Vinson[MVP]

SELECT Contacts.*
FROM Contacts
WHERE (((Contacts.DonorTypeID)="IN"));

Contacts fields:
ContactID
FirstName
LastName
OldLastName
Nickname
DonorTypeID
....

I have the control source as:
=Nz([Nickname],[FirstName]) & " " & Nz([OldLastName],
[LastName])

Thanks.

-----Original Message-----
On Mon, 28 Feb 2005 15:14:33 -0800, "Stephanie"
wrote:

Thanks.
I tried it but it returned: #Error

Please post the actual SQL of the query, and the actual
field names in
your table.

John W. Vinson[MVP]
.

On Tue, 1 Mar 2005 09:30:05 -0800, "Stephanie"
wrote:

I have the control source as:
=Nz([Nickname],[FirstName]) & " " & Nz([OldLastName],
[LastName])


Odd. That looks right to me. Try (for testing)

=Nz([Nickname], [FirstName])

Does this give the nickname if it exists, and the first name if it
doesn't?


John W. Vinson[MVP]