Eliminate "," if no first name in cancatenated expression

[Sue]

I have an expression as follows:

=lastname & ", " & firstname

but some of the expressions don't have a first name.

Can I chunk the comma in such a situation?


THANK YOU!!!

 

[Allen Browne]

Try:
= [lastname] & ", " + [firstname]

This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null

 

[Sue]

Thank you - That fixed it 100% (although I'm not clear on the subtleties
here...).
You've rescued me from the morass yet again - I appreciate your help, Allen!
--
Thanks for your time!

Try:
= [lastname] & ", " + [firstname]

This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have an expression as follows:

=lastname & ", " & firstname

but some of the expressions don't have a first name.

Can I chunk the comma in such a situation?


THANK YOU!!!

 

[David W. Fenton]

Try:
= [lastname] & ", " + [firstname]

This relies on a subtle difference between the 2 concatenation
operators in Access:
"A" & Null => "A"
"A" + Null => Null

But you don't want the ", " if there is no lastname. This works in
all cases:

Mid(("12" + LastName) & (", " + FirstName),3)

 

[Sue]

That looked like a cool trick, but it didn't work when I pasted your
expression into my report. Could an operand be missing?
--
Thanks for your time!

Try:
= [lastname] & ", " + [firstname]

This relies on a subtle difference between the 2 concatenation
operators in Access:
"A" & Null => "A"
"A" + Null => Null

But you don't want the ", " if there is no lastname. This works in
all cases:

Mid(("12" + LastName) & (", " + FirstName),3)

 

[David W. Fenton]

That looked like a cool trick, but it didn't work when I pasted
your expression into my report. Could an operand be missing?

I just pasted it into a query where the fields had that name, and it
worked perfectly. Are your fields named "LastName" and "FirstName"?
If not, you need to change it accordingly.