Discussion:
Concatenate Question
(too old to reply)
d***@gmail.com
2013-08-15 15:20:55 UTC
Permalink
i am trying to Concatenate within a query but sometimes there is no spouse in the record; therefore I don't want the & sign to show up between FName and Spouse. This is what I have now but the & sign shows up even if the spouse filed is empty.

Shareholders: [FName] & " & " & [Spouse] & " " & [LName]

Please help
Bob Barrows
2013-08-15 16:00:49 UTC
Permalink
Post by d***@gmail.com
i am trying to Concatenate within a query but sometimes there is no
spouse in the record; therefore I don't want the & sign to show up
between FName and Spouse. This is what I have now but the & sign
shows up even if the spouse filed is empty.
Shareholders: [FName] & " & " & [Spouse] & " " & [LName]
Please help
Iif provides a couple of options. Here is the easier to read IMO:
Shareholders: Iff(Nz([Spouse],"") = "", [FName] & " " & [LName]), [FName] &
" & " & [Spouse] & " " & [LName])
Debbie Pringle
2013-08-15 16:37:46 UTC
Permalink
Post by d***@gmail.com
i am trying to Concatenate within a query but sometimes there is no
spouse in the record; therefore I don't want the & sign to show up
between FName and Spouse. This is what I have now but the & sign
shows up even if the spouse filed is empty.
Shareholders: [FName] & " & " & [Spouse] & " " & [LName]
Please help
Shareholders: Iff(Nz([Spouse],"") = "", [FName] & " " & [LName]), [FName] &
" & " & [Spouse] & " " & [LName])


I copy and pasted exactly as you typed and it says invalid syntax or you need to enclose text data in quotes. What does that mean?
John W. Vinson
2013-08-15 16:27:56 UTC
Permalink
Post by d***@gmail.com
i am trying to Concatenate within a query but sometimes there is no spouse in the record; therefore I don't want the & sign to show up between FName and Spouse. This is what I have now but the & sign shows up even if the spouse filed is empty.
Shareholders: [FName] & " & " & [Spouse] & " " & [LName]
Please help
A sneaky trick is to use the fact that both the & and + operators concatenate
strings, but they handle NULL differently: "String" & NULL returns just
"String", but "String" + NULL returns NULL. Try

[FName] & (" & " + [Spouse]) & [LName]

And get ready for some complaints; my name's John Vinson, my wife is Karen
Strickler.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Debbie Pringle
2013-08-15 16:57:55 UTC
Permalink
Post by John W. Vinson
Post by d***@gmail.com
i am trying to Concatenate within a query but sometimes there is no spouse in the record; therefore I don't want the & sign to show up between FName and Spouse. This is what I have now but the & sign shows up even if the spouse filed is empty.
Shareholders: [FName] & " & " & [Spouse] & " " & [LName]
Please help
A sneaky trick is to use the fact that both the & and + operators concatenate
strings, but they handle NULL differently: "String" & NULL returns just
"String", but "String" + NULL returns NULL. Try
[FName] & (" & " + [Spouse]) & [LName]
And get ready for some complaints; my name's John Vinson, my wife is Karen
Strickler.
--
John W. Vinson [MVP]
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Can I ask what did wrong that will cause complaints?
John W. Vinson
2013-08-15 22:21:49 UTC
Permalink
On Thu, 15 Aug 2013 09:57:55 -0700 (PDT), Debbie Pringle
Post by Debbie Pringle
Can I ask what did wrong that will cause complaints?
Sorry that wasn't clear! It's just that in the 21st century there are quite a
few women who choose not to use their spouse's last name; so if your database
forces you to (say) address me and my wife as "John & Karen Vinson" instead of
"John Vinson & Karen Strickler", she's going to be a little bit miffed. She's
used to it of course but... the times they are a-changin.

This will of course be more of an issue in some communities than others; it
may be that your membership won't have any concerns at all.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Debbie Pringle
2013-08-15 16:58:36 UTC
Permalink
Thank you John. It worked just fine.
Post by John W. Vinson
Post by d***@gmail.com
i am trying to Concatenate within a query but sometimes there is no spouse in the record; therefore I don't want the & sign to show up between FName and Spouse. This is what I have now but the & sign shows up even if the spouse filed is empty.
Shareholders: [FName] & " & " & [Spouse] & " " & [LName]
Please help
A sneaky trick is to use the fact that both the & and + operators concatenate
strings, but they handle NULL differently: "String" & NULL returns just
"String", but "String" + NULL returns NULL. Try
[FName] & (" & " + [Spouse]) & [LName]
And get ready for some complaints; my name's John Vinson, my wife is Karen
Strickler.
--
John W. Vinson [MVP]
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
Loading...