Hide report label if bound text box is null 3

[Emblem1]

If a bound text box is null of data, how can I hide a label placed near it?

I tried doing a search on this and other forums, but didn't find anything specific about it.

Thanks.

 

[PaulBricker]

I would say you would use the Format event in the report section the textbox is found in.

If IsNull(me.textboxname) Then
Me.Label.Visible = False
Else
Me.Label.Visible = True
End If


HTH

Paul

 

[dhookom]

You can do this without code by changing the label to a text box with a control source like:
="Spouse Name " + [SpouseName]
If the SpouseName field is Null then the quoted text will not display. Make sure the text box is sized correctly and not allowed to grow.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emblem1]

Thanks both.

Now I have another question.

In the same report have a date field [LastPMDate] on the report, with a label near it 'PM Date'. If the date is over 1 year old, I want to change the label to say 'PM Date - PM Due!' in Bold and Red (normal is orange, not bold). If the date is Null, then I would like the label to say 'No PM Date on Record' in Red, but not bold.

 

[dhookom]

Change the label to a text box with a control source using IIf() to switch the displayed values. Another option is to stack more than one label and make them visible or invisible with code modified from PaulBricker's reply.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emblem1]

Thanks Duane.

I havent used IIF before, so I would need help with the code itself. I want to keep it visible as this information as the most important part of the report.

 

[PaulBricker]

I'm not sure what the advantage is using a textbox over a label other than typing. I'm sure Duane has more knowledge here than I do, but I think an IIF statement would be cumbersome to use here so I'll give you the vba to do it for a label.

In the Format event for the section the label/textbox is in you would put something like this. Change LabelName to the name of your label.

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
If [LastPMDate] = DateSerial(Year([LastPMDate])-1,Month([LastPMDate]),Day([LastPMDate])) > 365 Then
Me.LabelName.Caption = "'PM Date - PM Due!"
Me.LabelName.FontWeight = 900
Me.LabelName.ForeColor = vbRed
ElseIf IsNull(Me.[LastPMDate]) Then
Me.LabelName.Caption = "No PM Date on Record"
Me.LabelName.ForeColor = vbRed
Me.LabelName.FontWeight = 400
Else
Me.LabelName.Caption = "PM Date"
Me.LabelName.ForeColor = vbOrange
Me.LabelName.FontWeight = 400
End If
End Sub

Paul

 

[PaulBricker]

Sorry, the first If statement need to be adjusted.

If [LastPMDate] - DateSerial(Year([LastPMDate])-1,Month([LastPMDate]),Day([LastPMDate])) > 365 Then

It should be a minus sign, not an equals sign.

Paul

 

[Emblem1]

It gave a runtime error 2465
Can't find the field 'Forms' referred to in your expression.

I Tried to change the label to a text box, but same error.

Here is the code as you suggested I placed in the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [LastPMDate] = DateSerial(year([LastPMDate]) - 1, month([LastPMDate]), day([LastPMDate])) > 365 Then
Me.PMDate.Caption = "'PM Date - PM Due!"
Me.PMDate.FontWeight = 900
Me.PMDate.ForeColor = vbRed
ElseIf IsNull(Me.[LastPMDate]) Then
Me.PMDate.Caption = "No PM Date on Record"
Me.PMDate.ForeColor = vbRed
Me.PMDate.FontWeight = 400
Else
Me.PMDate.Caption = "PM Date"
Me.PMDate.ForeColor = vbOrange
Me.PMDate.FontWeight = 400
End If
End Sub

The debugger points to the if statement.

 

[Emblem1]

Darn, tried the adjustment but same error.

 

[PaulBricker]

Other then changing the = sign to -, in this line

Me.PMDate.Caption = "[red]'[/red]PM Date - PM Due!"

I notice an addtional ' (single quote mark). Try removing that, if it's not just a typo.
Also, make sure you have the correct name for your label. The name you are using seems more like a textbox name. Usually, the label name has the word label in it someplace. Like PMDate_Label

Paul

 

[Emblem1]

I am running Access2000 if it matters.

I changed it to a label, renamed it PMDate_label, and updated the code to reflect the label name. I also removed the extra '. Same error. The debugger still highlights the IF statement code line.

I was really hoping to get this to work. I do appreciate your time.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [LastPMDate] - DateSerial(year([LastPMDate]) - 1, month([LastPMDate]), day([LastPMDate])) > 365 Then
Me.PMDate_label.Caption = "PM Date - PM Due!"
Me.PMDate_label.FontWeight = 900
Me.PMDate_label.ForeColor = vbRed
ElseIf IsNull(Me.[LastPMDate]) Then
Me.PMDate_label.Caption = "No PM Date on Record"
Me.PMDate_label.ForeColor = vbRed
Me.PMDate_label.FontWeight = 400
Else
Me.PMDate_label.Caption = "PM Date"
Me.PMDate_label.ForeColor = vbOrange
Me.PMDate_label.FontWeight = 400
End If
End Sub

 

[PaulBricker]

Well, I googled the error and got these hits

[google] runtime error 2465[/google]

It appears the report is having issues with naming. First, I would delete the label from the report, and put a new one on. Let Access name it. Then change the name in your code to the new name and see if that helps.

The IF statement looks fine. There should be no issues with that. I'm running A2000 as well so that's not the problem. Let me know if putting a fresh label on the report helps.

Paul

 

[Emblem1]

If I could send you the entire DB I would. Its small and not confidential.

Anyway, I deleted it and replaced it. Access named it Label34. I matched the code to it. Same error.

 

[PaulBricker]

p
bricker
at
comcast
dot
net

put it all together and I'll see what I can do. Probably be tomorrow before I get to work on it.


Paul