Concatenate Question


D

donovanspark401

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
 
Ad

Advertisements

B

Bob Barrows

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])
 
J

John W. Vinson

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
 
D

Debbie Pringle

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])


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?
 
D

Debbie Pringle

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


Can I ask what did wrong that will cause complaints?
 
D

Debbie Pringle

Thank you John. It worked just fine.

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
 
Ad

Advertisements

J

John W. Vinson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top