A lingering question about suppressing ", " in concatenated expression (=lastname&", "&firstname)

[Sue]

I'm a bit slow on the uptake. 8 days later this is still puzzling me...
I love what Allen shared - truly - and it solved my problem, but I have
another question.
When writing an expression in Access, as I understand it, whatever lies
between quotation marks is to be displayed by Access as literal characters.
The expression you gave me worked beautifully, but I'm trying to understand
why it does.
I understand this expression to still include the literal characters - a
comma followed by a single space. (These are enclosed within the quotation
marks). Does the Null following this essentially tell Access to disregard
the literal characters which follow the ampersand?

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

There is a subtle difference between the 2 concatenation operators:
"A" + Null => Null
"A" & Null => "A"

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

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

I have a field in my report -

=[lastname] & ", " & [firstname]

I'd like to suppress the ", " when there is no first name... some of my
contacts are offices/businesses/departments & don't have first names.

Any way to do this???

Thanks

 

[Allen Browne]

Hi Sue

I used an old trick that's probably worth knowing. There's a subtle
difference between the 2 concatenation operators in Access. Open the
Immediate Window (Ctrl+G), and enter:
? "A" & Null
You get A.
Now try:
? "A" + Null
This time, you get Null.

So:
"A" & Null => "A"
"A" + Null => Null

In general, you want to use &, since + is ambiguous, i.e. it is also used
for numeric addition, so if a field contains a number Access may attempt
addition and the expression might fail unless you explicitly typecast it.
But not very many names are numeric so it's fairly safe for the
expression you wanted, and it does suppress the trailing comma and space
when FirstName is null.

Of course, you will get a leading comma and space is lastname is null and
firstname is not, but that's probably desirable.

Hope that explains it. Sometimes it's hard to know how much to give based
on a few lines of question, so you are always welcome to post back if you
want to understand how something works.

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

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

I'm a bit slow on the uptake. 8 days later this is still puzzling me...
I love what Allen shared - truly - and it solved my problem, but I have
another question.
When writing an expression in Access, as I understand it, whatever lies
between quotation marks is to be displayed by Access as literal
characters.
The expression you gave me worked beautifully, but I'm trying to
understand
why it does.
I understand this expression to still include the literal characters - a
comma followed by a single space. (These are enclosed within the quotation
marks). Does the Null following this essentially tell Access to disregard
the literal characters which follow the ampersand?

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

There is a subtle difference between the 2 concatenation operators:
"A" + Null => Null
"A" & Null => "A"

I have a field in my report -

=[lastname] & ", " & [firstname]

I'd like to suppress the ", " when there is no first name... some of my
contacts are offices/businesses/departments & don't have first names.