I'm sure this is a stupid question.

[Sue]

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]

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

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

 

[Sue]

That worked! Thanks a million!
Subtlety generally eludes me.

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

 

[Larry Linson]

The only "stupid" question is a question to which one does not get an answer
because it went unasked. (The solution that Allen provided is decidedly
UNobvious; I'm sure it could be searched out, but not easily.)

Larry Linson
Microsoft Access MVP

 

[Sue]

I'm a bit slow on the uptake. 8 days later this is still puzzling me...
I love this trick - truly - and it solved my problem beautifully, 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

 

[Rick Brandt]

I'm a bit slow on the uptake. 8 days later this is still puzzling
me... I love this trick - truly - and it solved my problem beautifully, 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?

Anything at all + Null = Null

Doesn't matter what "Anything at all" consists of. Quoted strings, variables,
numbers, etc.. Combine them with Null using the Plus operator and the result is
Null. Same is true of minus, times, division, really any operator I can think
of except for &.

 

[Allen Browne]

Answered your new post for this q.