Hiding Label and Text Box in Report if Text Box is Null

[scidb]

Hello All,

I hope you can help with this question. I have seen numerous threads with very similar or the same issue, but I cannot get the solutions to work for me. In a report detail section I have a function that I want to hide a text box and the corresponding label if the text box has a null value. Here is what I have:

Function hideNullFields()
If IsNull(Me.txtDescription) Then

Me.lblDescription.Visible = False
Me.txtDescription.Visible = False
Else
Me.txtDescription.Visible = True
Me.lblDescription.Visible = True

End If
End Function

In the On Format Event of the Report Detail section I have =hideNullFields().

This function is for one of the eight label/textbox pairs I have in the detail section, but I really need this to apply to all eight pairs. Can anyone offer any suggestions? Any information would be greatly appreciated!
Thanks for your time.

 

[dhookom]

If a text box is Null then it won't display any value. This might be the same as making it invisible. You can make a label invisible, without any code by changing the label to a text box and setting its control source property to:
="Description " + [Description]
Make sure the size of the "label" text box doesn't grow and doesn't show more than desired. In the expression above, if Description is Null then the entire text box value will be null.

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]

 

[scidb]

Thanks for your suggestion, unfortunately it is not working for me. Using your method I still see the word "Description" when the field [Description] is null. I must be missing something.

You are correct, if the text box has no value, nothing is displayed...but I wanted to make the text box frame as well as the corresponding label frame (with caption of "Description") invisible. In addition I was hoping that the white space they once occupied would "collapse".

 

[dhookom]

I didn't realize you were using visible borders. I did just try my solution and set both text boxes to Can Shrink Yes and set the report section to Can Shrink. If the Description field is Null then the boxes did not appear and the section shrank.

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]

 

[scidb]

Hi dhookom,

Thanks for your feedback, unfortunately I must be doing something wrong, because it is not working for me. Do you have any suggestions as to where I might look for a problem? I have set the appropriate text boxes to Can Shrink - Yes. Could it have something to do with my Border Width being hairline? Does my border Style have to be transparent (or not showing)?

I would be willing to give another possible solution a try.

Thanks again for your time.

 

[dhookom]

It's not an issue with the border width. Are you sure the field value is null and not something else?

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]

 

[ZmrAbdulla]

Try this thread703-977373

Zameer Abdulla
Visit Me

 

[scidb]

dhookom and ZmrAbdulla,

Thanks so much for responding. I started down a slightly different path which will hopefully lead to the same result. Using suggestions from thread703-415220, I have changed mylabels to text boxes and used the following "code" in the control source for the "Label" text box : =IIf([Description]<>"","My Text","")

This worked fine, until I added about my fifth control with this type of code and then I started to see #Name? instead of "My Text". It almost seems like a bug because #Name? appeared not only for the "Label" I just added, but also for previous "Labels" which before displayed "My Text" fine. My report is based on a query and the Field names are generated as aliases, but they have no name conflict with any of the controls in the detail section of my report! Any suggestions?

PS. dhookom, I did check that my field values are truly null and not a space, etc.

 

[misscrf]

not to butt in but I saw that dhookm also suggested making sure the detail section was set to can shrink, but I did not see whether that suggestion was tried out. Might be something to check.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I don't understand how [red]=IIf([Description]<>"","My Text","")[/red] could work since "" does not equal Null. I would use [red]=IIf([Description] & ""<>"","My Text",Null)[/red].

However my earlier suggestion should have worked except that it seems your field value might be "" rather than Null.


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]

 

[scidb]

misscrf,

Thanks for you input, I did not mention it, but I did set the detail section property Can Shrink to yes as well as the individual "Label" text boxes and data text boxes. I started to build the report from scratch and I now have the "Labels" and text boxes being hidden depending on the content of the data text boxes. There may have been something in the report detail section that I did not see that was causing the #Name? issue.

dhookom,

With regard to what you mentioned <I don't understand how =IIf([Description]<>"","My Text","") could work since "" does not equal Null>, I agree, but it works?! I was trying to find a VB code solution initially, but nothing seemed to work. This could be because I am a newbie to VB of course! For example, referring to the original code I posted, the TRUE/FALSE options for the Visible property in the code produced absolutely no results, but when I substituted in YES/NO, it indiscriminantly supressed all the lines (but it had an affect!). Not entirely sure why my report is working, but it is at this point. Thanks to all of you for your feedback.

A final question, does anyone have any information on the posssibility of selectively formating subscripted characters in MS Access? For example, in H2O the "2" would be subscripted?

 

[dhookom]

It seems clear to me that you are storing a zero-length-string rather than Null in the field. There is a huge difference between the two types of values.

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]