Need some help getting rid of a comma in my mailing labels.

[SomeGuy]

On some mailing labels, there is a need for a department name and the Institution name. I changed them slighly so that a comma would not show up if the institution was not entered. This was the expected problem. However, I have now come across a situation where there is an Institution name but no department name.

The label will print like this now:
, Institution

How can I make the comma only show up if both of the names are there?

=Trim([Department] & IIf(IsNull([Institution]),"",", " & [Institution]))

 

[Fornatian]

Following your earlier logic, the way I got round it was to build myself a custom function that returned a string with/without the comma.

Public Function AddComma(strFieldName As String) As String
If IsNull(strFieldName) Then
AddComma = "" 'if null then return a zero-length string
Else
AddComma = strFieldName & ", " 'if not null add a comma and space
End If

Hope that helps...

ian

 

[Chris RR]

Hi,
You can also consider the + operator. It would work something like this:
=Trim([Department] & ", " + Trim([Institution]))

Access will only do the concatenation if Institution is not null.

 

[SomeGuy]

Hi! Thanks for your responses.

Fornation: that is one possibility but when I was messing around last night I came up with the following:

=Trim(IIf(IsNull([Department]),[Institution],([Department] & IIf(IsNull([Institution]),"",", " & [Institution]))))

This seems to do the trick so that if only Department or only Institution are entered, the comma is absent.

Chris RR: I tried out your code but it still puts a comma before the Institution if the Department is not entered. Sigh. Talk about having my cake and eating it too!Anyway, thanks for the tidbit about concatenation as I didn't know that.

[This message has been edited by SomeGuy (edited 11-22-2000).]