Concatenate Question

[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

 

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

 

[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

 

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

 

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

 

[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

 

[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