Formatting labels report

[LaurieW]

I am trying to format 2 fields on a labels report and can't quite get it...

The 2 fields are ContactName and Title.

If the ContactName is blank, I just want the title and vice versa. But if both fields have data, I want to put comma space between them. This is what I have currently as code, which works only if there isn't a title. I have tried a myriad of other code like this, but can't get it to work.

Function AddComma()
If (Me.ContactName = "") & (Me.Title <> "") Then AddComma = Me.Title
ElseIf (Me.ContactName <> "") & (Me.Title = "") Then AddComma = Me.ContactName
Else: AddComma = Me.ContactName & ", " & Me.Title
End If
End Function

 

[jatfill]

You could also just try the following as opposed to using a function. This works pretty well with mailing labels, etc. as the report field's Control Source:


=IIf(IsNull([ContactName]), [Title], IIF(IsNull([Title]), [ContactName], [ContactName] & ", " & [Title]))

 

[LaurieW]

I've already tried all that stuff ... it doesn't work. If there is no name and a title it puts a comma space and title like this:

, News Director

!!! That's why I tried the function thing. Thought it would be easier! Ha!

Any other thoughts or ideas? Thanks for your help....

 

[jatfill]

are you sure? I just tested it and the statement I posted above displayed the following results:

(No Title) = John Doe
(No Name) = Access Programmer
(Name and Title) = Jane Smith, President

 

[boblarson]

Make sure that your textboxes on your report ARE NOT named the same as your Table Field Names.

I can't stress that enough. If the textbox names are the same as your field names, when you put ANY formula in the Recordsource property that uses those names, you will only get #Error in the textbox.

So, try renaming your textboxes and then the code shown by jatfill should work.

BL
hth

 

[LaurieW]

I don't understand ... my text box is named "text2". I am typing the code into the control source of the text box. Is this incorrect???

Thanks....

 

[LaurieW]

This is what I've found now ...
the code from jatfill works fine IF you just use records from straight from a table. I am getting my report data from a query, that is combining [Last Name] and [First Name] fields to make [ContactName]. This must have something to do with it. Anyone have any ideas why though? .....

 

[LaurieW]

I figured out why jatfill's code doesn't work. It's because my concatenated Name field had a space in it and was not null.

When I say IIf([ContactName]=" " ... then it works.

Glad I figured that out; what a struggle!