Eliminate "," if no first name in cancatenated expression

  • Thread starter Thread starter Sue
  • Start date Start date
S

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!!!
 
Try:
= [lastname] & ", " + [firstname]

This relies on a subtle difference between the 2 concatenation operators in
Access:
"A" & Null => "A"
"A" + Null => Null
 
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!


Allen Browne said:
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.

Sue said:
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!!!
Click to expand...
Click to expand...
 
Try:
= [lastname] & ", " + [firstname]

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

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

Mid(("12" + LastName) & (", " + FirstName),3)
 
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!


David W. Fenton said:
Try:
= [lastname] & ", " + [firstname]

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

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

Mid(("12" + LastName) & (", " + FirstName),3)
Click to expand...
 
That looked like a cool trick, but it didn't work when I pasted
your expression into my report. Could an operand be missing?
Click to expand...

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.
 
Back
Top