How to hide comma if there is no value

[accessLearn]

I have an ms access report where I have an invisible combo box that gets the value for the employee name and role from the query which selects data from tblemployee.

I am using this box to display name and role in the text box for report using the following code
=Combo58.Column(1) & ", " & Combo58.Column(2) in the control source property.
The issue is that if there an employee name and role, then it looks fine but If there is no nameand role then it display “,” in the report.

I want to display “,” only when I have data in the combo box for employee name and role, I tried using Nz function but it is not working.
I would appreciate any suggestion.

Thank you,


Blyzzard

[orange]

You could try (UNTESTED)

=IIf ( len(me.Combo58.Column(1))> 0 And len(me.Combo58.Column(2)) >0, Combo58.Column(1) & ", " & Combo58.Column(2), "")

[June7]

So what did you use Nz() on - the combobox or the column references?

Any expression involving math operators and Null will return Null. The + is a very special operation character. It not only does number addition but also concatenates. So if you '+' text data and any field is Null, the expression will return Null. In some cases using + instead of & can deal with what you are encountering.

Problem is, combobox list columns are always text which means an empty string instead of Null if there is no data. Try an IIf()

IIf([Combo58] Is Null, Null, Combo58.Column(1) & ", " & Combo58.Column(2))

[accessLearn]

Thank you. It worked!!