Concatenating Names on Mail Labels

[rgeoffb]

Concanting Names on Mail Labels

Would like to join names on a mailing label:

ie: =Trim([Firstname] & " & " & [Spouse] & [Lastname])

Problem is not everyone has a spouse. How do I get Access to ignore the " & " and [Spouse]?

Thanks

 

[ChrisO]

Give this a shot: -

Trim([Firstname] & IIf(Nz([Spouse], "") = "", "", " & " & [Spouse]) & " " & [Lastname])

Hope that helps.

Regards,
Chris.

 

[rgeoffb]

Worked like a champ! Thanks

 

[pbaldy]

This may also work:

=Trim([Firstname] & (" & " + [Spouse]) & " " & [Lastname])

 

[ChrisO]

G’day Paul, fancy meeting you here.

This may also work:

It may but is doesn’t seem too…

Test case: -

Option Explicit
Option Compare Text


Sub Test()
    [color=green]' All are intended to be Variants so they may recieve Nulls.[/color]
    Dim Firstname, Spouse, Lastname As Variant

    Firstname = "Fred"
    Spouse = "Mary"
    Lastname = "Nurks"
    
    MsgBox Trim([Firstname] & IIf(Nz([Spouse], "") = "", "", " & " & [Spouse]) & " " & [Lastname])

    MsgBox Trim([Firstname] & (" & " + [Spouse]) & " " & [Lastname])

End Sub

From the original question I could not determine if the result was required in SQL or VBA.
It may work in SQL but it doesn’t work in VBA so I went the path of worst case…VBA.
The reason for the Nz function was to convert any possible Null to a ZLS and then process the data.
Also, propagation of Nulls with the + sign can be somewhat tricky at times.

VBA seems to be less tolerant of such things.

Regards,
Chris.

 

[pbaldy]

G'Night my friend! (is that proper Aussie?)

Well, it was untested, but I copied your test into a module, changed the msgboxes to debug.print.

Spouse = "Mary"

produces:

Fred & Mary Nurks
Fred & Mary Nurks

Spouse = Null

produces:

Fred Nurks
Fred Nurks

Have I done something wrong, since this seems to do what I thought it would?

 

[ChrisO]

G'Night my friend! (is that proper Aussie?)

No.

It is 4:150PM here so the diplomatic expression is either: -

G’arvo… &

your Highness &

IOR

,go to bed dick head &

IOR

,wanker

There are a few variations that should not be posted.

Back to the question…

Did you try…

Spouse = ""

A ZLS?

Regards,
Chris.

 

[pbaldy]

You have a sick mind, my friend...that's probably why I like you so much! It's 10:30pm here, so that's why I tried the G'Night. Given your options, this dick head will probably go to bed soon.

I didn't test it with a ZLS, and it certainly won't handle one elegantly. I was thinking that the issue was about nulls, and I thought you were saying that mine wouldn't handle nulls properly. If the data could contain ZLS, then your solution is clearly superior (which I should have anticipated; you're always out-thinking me!).

 

[ChrisO]

Sweet dreams Paul.

I think you are better at SQL, I just dabble in code.

Good night my friend, and regards,
Chris.