IIf

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

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)
 
Is this not working?
Are you getting an error? If so, what?
Is this in a form or a report?


| 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)
 
Stephanie said:
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.
Click to expand...

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)
Click to expand...

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.
 
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)
Click to expand...

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-----
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.
Click to expand...

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!
Click to expand...
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)


.
Click to expand...
 
Thanks.
I tried it but it returned: #Error
-----Original 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)
Click to expand...

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]
.
Click to expand...
 
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.
 
I have the control source as:
=Nz([Nickname],[FirstName]) & " " & Nz([OldLastName],
[LastName])
Click to expand...

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]
 
Same #Error

I don't understand this- I have many queries that use:
=Nz([Nickname], [FirstName]) and that expression returns
the correct response.

Now I get it. =Nz([Nickname], [FirstName]) works IF there
is a Nickname (returns Nickname), but if there is no
Nickname it doesn't work (returns #Error). I think this
is why I ended up using IIf in the first place, but still
can't figure out the IIf syntax to take OldLastName into
considertion.

Thanks.
-----Original Message-----
I have the control source as:
=Nz([Nickname],[FirstName]) & " " & Nz([OldLastName],
[LastName])
Click to expand...

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]
.
Click to expand...
 
Same #Error

I don't understand this- I have many queries that use:
=Nz([Nickname], [FirstName]) and that expression returns
the correct response.

Now I get it. =Nz([Nickname], [FirstName]) works IF there
is a Nickname (returns Nickname), but if there is no
Nickname it doesn't work (returns #Error).
Click to expand...

That's VERY odd, because that's exactly what NZ should do - detect the
case where Nickname is empty!
I think this
is why I ended up using IIf in the first place, but still
can't figure out the IIf syntax to take OldLastName into
considertion.
Click to expand...

You need *TWO* IIF statements. The NickName and the OldLastName are
two independent unrelated issues; each must be handled separately.

Try:

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


John W. Vinson[MVP]
 
Stephanie said:
Same #Error

I don't understand this- I have many queries that use:
=Nz([Nickname], [FirstName]) and that expression returns
the correct response.
Click to expand...

Just a thought: what's the name of the control bound to this
expression? It must be different from the name of any of the fields
involved.
 
Thanks for the help!

Here's my example:
FirstName: Susan
Nickname: Susie
OldLastName: Quayle
LastName: Anderson

So I want to see Susie Anderson.

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

Gives me Susie Quayle. So now I have the Nickname
correct, but playing around with the 2nd IIF statement, I
still couldn't get the LastName correct.

What does ""="" mean? Thanks, Stephanie

-----Original Message-----
Same #Error

I don't understand this- I have many queries that use:
=Nz([Nickname], [FirstName]) and that expression returns
the correct response.

Now I get it. =Nz([Nickname], [FirstName]) works IF there
is a Nickname (returns Nickname), but if there is no
Nickname it doesn't work (returns #Error).
Click to expand...

That's VERY odd, because that's exactly what NZ should do - detect the
case where Nickname is empty!
I think this
is why I ended up using IIf in the first place, but still
can't figure out the IIf syntax to take OldLastName into
considertion.
Click to expand...

You need *TWO* IIF statements. The NickName and the OldLastName are
two independent unrelated issues; each must be handled separately.

Try:

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


John W. Vinson[MVP]
.
Click to expand...
 
Thanks, Dirk

This is a text box: NameShown
The Control Source is the Nz or IIF statements I've been
trying.

Stephanie
-----Original Message-----
Same #Error

I don't understand this- I have many queries that use:
=Nz([Nickname], [FirstName]) and that expression returns
the correct response.
Click to expand...

Just a thought: what's the name of the control bound to this
expression? It must be different from the name of any of the fields
involved.

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

(please reply to the newsgroup)


.
Click to expand...
 
Thanks for the help!

Here's my example:
FirstName: Susan
Nickname: Susie
OldLastName: Quayle
LastName: Anderson

So I want to see Susie Anderson.

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

Gives me Susie Quayle. So now I have the Nickname
correct, but playing around with the 2nd IIF statement, I
still couldn't get the LastName correct.

What does ""="" mean? Thanks, Stephanie
Click to expand...

Since NZ isn't working, I'm using an alternative: concatenating the
[OldLastName] value to an empty string "", and checking to see if the
result is an empty string. It will be if [OldLastName] is either a
zero-length string or a NULL.

You say it's not working. What are you getting for the lastname? Have
you tested it with records with nothing in Nickname, and/or nothing in
OldLastName?

John W. Vinson[MVP]
 
Thanks for the lesson.

The results for someone with no Nickname, FirstName =
Collette, LastName = Champion and no OldLastName is
correct: Collette Champion.

No Nickname, First Name = Donna, OldLastName = Sellers,
LastName = Black incorrectly returns: Donna Sellers.


-----Original Message-----
Thanks for the help!

Here's my example:
FirstName: Susan
Nickname: Susie
OldLastName: Quayle
LastName: Anderson

So I want to see Susie Anderson.

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

Gives me Susie Quayle. So now I have the Nickname
correct, but playing around with the 2nd IIF statement, I
still couldn't get the LastName correct.

What does ""="" mean? Thanks, Stephanie
Click to expand...

Since NZ isn't working, I'm using an alternative: concatenating the
[OldLastName] value to an empty string "", and checking to see if the
result is an empty string. It will be if [OldLastName] is either a
zero-length string or a NULL.

You say it's not working. What are you getting for the lastname? Have
you tested it with records with nothing in Nickname, and/or nothing in
OldLastName?

John W. Vinson[MVP]
.
Click to expand...
 
Thanks for the lesson.

The results for someone with no Nickname, FirstName =
Collette, LastName = Champion and no OldLastName is
correct: Collette Champion.

No Nickname, First Name = Donna, OldLastName = Sellers,
LastName = Black incorrectly returns: Donna Sellers.
Click to expand...

In your original post you said:

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

There is an OldLastName. It's Sellers. The query is returning the
OldLastName because it is not null.

The query is doing *what you asked for* - is that perhaps something
different from what you want?

John W. Vinson[MVP]
 
Yes, sorry. As Dirk pointed out I contracted myself in
the first post. I always want to use LastName. Hello! I
just explained it to myself. It's working now and I
really appreciate the lessons of nz, IIF, when to use 2
statements, zero-length string and null. Thanks and
sorry for the drama. Stephanie
 
Back
Top