Change visibility of label based on existence of data in another field 2

[annsolomon]

I have a Work Order report with fields for the customer's home phone, work phone, and work phone extension. Frequently, we don't receive the customer's work phone. When that happens, my report shows the Work Phone label and an empty space beside it which is okay. But beyond that empty space (where the work phone would appear if we had one) is the Ext label.

I don't want the Ext label (which is named Label65) to be visible if there is no work phone but I can't figure it out.

I've tried:

IIF(IsNull([WorkPhone]),[Label65]= "",[Label65])

I've tried:

If [WorkPhone] = "" Then
[Label65].Visible = False
End If


I've tried:

If len([WorkPhone]) = 0 Then
[Label65].Visible = False
End If

Depending upon which "solution" I try, I either get error 2427 "you entered an expression that has no value" or I get no error message but the label appears on the report.

Can somebody point me in the right direction? Both the WorkPhone and Ext fields are set as text fields.


Ann

 

[dhookom]

Your code would need to be in the On Format event of the section containing the controls. I would also change the name of the label to "lblWorkExt" and use:
Me.lblWorkExt.Visible = Not Null(Me.WorkPhone)

Duane
MS Access MVP

 

[annsolomon]

Duane, thanks for the tip. I'm using DAO rather than ADO so I ended up with Me.lblWorkExt.Visible = Not IsNull(Me.WorkPhone).

I'm back in business now. Thanks a lot.

Ann

 

[dhookom]

Sorry, I didn't proof my reply. "IsNull()" is the correct syntax.

Duane
MS Access MVP