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 |
#11
|
|||
|
|||
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----- 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] . |
#12
|
|||
|
|||
On Wed, 2 Mar 2005 06:56:00 -0800, "Stephanie"
wrote: 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). 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. 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] |
#13
|
|||
|
|||
"Stephanie" wrote in message
Same #Error I don't understand this- I have many queries that use: =Nz([Nickname], [FirstName]) and that expression returns the correct response. 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) |
#14
|
|||
|
|||
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----- On Wed, 2 Mar 2005 06:56:00 -0800, "Stephanie" wrote: 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). 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. 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] . |
#15
|
|||
|
|||
Thanks, Dirk
This is a text box: NameShown The Control Source is the Nz or IIF statements I've been trying. Stephanie -----Original Message----- "Stephanie" wrote in message Same #Error I don't understand this- I have many queries that use: =Nz([Nickname], [FirstName]) and that expression returns the correct response. 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) . |
#16
|
|||
|
|||
On Wed, 2 Mar 2005 14:15:44 -0800, "Stephanie"
wrote: 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 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] |
#17
|
|||
|
|||
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----- On Wed, 2 Mar 2005 14:15:44 -0800, "Stephanie" wrote: 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 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] . |
#18
|
|||
|
|||
On Wed, 2 Mar 2005 16:38:41 -0800, "Stephanie"
wrote: 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. 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] |
#19
|
|||
|
|||
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 -----Original Message----- On Wed, 2 Mar 2005 16:38:41 -0800, "Stephanie" wrote: 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. 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] . |
|
Thread Tools | |
Display Modes | |
|
|