Make null values in a report not visible 2

[OPathMan]

I am trying to hide fields in a report (both the label and the text box)if the field is null (blank). I thought the best way would be to have a macro make the field "not visible" if null. I have tried various permutations using the IIf command in the SetValue macro option without success. Any help with syntax would be appreciated.

 

[danvlas]

If the label is attached to the control, then it will inherit control's visibility. If not, you have to set each of them separately.

In the Format event of the section:

Me("ControlName&quot.Visible = Not IsNull(Me("ControlName&quot
Me("LabelName&quot.Visible = Me("ControlName&quot.Visible

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[OPathMan]

Thanks Daniel! However, my own limitations are preventing this from working. A couple of concerns: 1) the parantheses after the equal sign do not seem symmetrical as to open/close. I'm not sure if this is a problem; 2) if the table field is "COMMENT" and the corresponding report text box in the report is also "COMMENT", should the string read?:

Me(Reports![Cases]![COMMENT]).Visible = Not IsNull(Me(Reports![Cases]![COMMENT])

I get a text box message that says, MS Access can't parse the expression.

Thanks again.

 

[Cinni]

I don't know if it was a typo or not but your missing a closing parenthesis

 

[RoyVidar]

Hi!

Using Danvlas syntax:

[tt]Me("COMMENT&quot.Visible = Not IsNull(Me("COMMENT&quot)[/tt]

Me - refers to the report (Reports!ReportName might be ommitted)
ControlName - refers to just the name of the control/textbox...

Which is similar to:
[tt]Me!COMMENT.Visible = Not IsNull(Me!COMMENT)[/tt]

HTH Roy-Vidar

 

[danvlas]

Yes, it was a typo...
Me("Comment&quot.Visible = Not IsNull(Me("Comment&quot)

Roy has put it right. However, in code I prefer the other notation, which allows much more flexibility (can use variables instead of hardcoded strings for control names)

Use either Me or Reports![ReportName].
Not both at the same time, as it won't work.

Me is a keyword that refers to the form or report where the code resides.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[carlosguill]

This is the same kind of problem I am having, I am trying to hide null/blank fields in a report. I am very new to using access and I would like to ask where is that you use this code, in a macro or in a module and you apply it to the report? I will appretiate your answers.

 

[RoyVidar]

Hi!

Short answer: In the on format event of the section in which your controls reside.

Little more explanation:
Find the section of the report in which the controls reside (Detail section?). Right click the section, select properties. Code such as this would be placed in the On Format event of the section. Click the button with three dots on to the right on the On Format line. Select Code Builder.

HTH Roy-Vidar

 

[dhookom]

The code would be placed in the On Format event of the report section that contains the controls.

If you don't want to use code, you can change the label to a text box and set the control source to:
="Comment " + [Comment]
Make sure the text box only displays the word Comment and is set to not grow.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[carlosguill]

Thank you Roy for your answer, you led me in the right direction, except that my problem is a little more complicated because my database has about 100 variables so I would like to set this procedure for all the fields, because the null fields are random for each record. Is this posible to do?
Carlosg

 

[RoyVidar]

Hi again!

I've never done that in a report before, but... I just tested this little routine that should work on all textboxes in a report, but no warranties (did at least work in my version;-)).

[tt]dim ctl as control
for each ctl in me.controls
if typeof ctl is textbox then
ctl.visible=not isnull(ctl.value)
end if
next ctl[/tt]

Roy-Vidar

 

[carlosguill]

I tried this code with one of the fields in my report and I got the following error message, "the control number specified is greater than the number of controls". Any sugestions of what I am doing wrong?
Than you.

 

[carlosguill]

Just to clarify, the code I tried was the previous one.
Carlosg

 

[RoyVidar]

He he - was just seconds away from posting something on the second thingie;-)

With previous one, I assume Danvlas suggestion?

I don't know what causes the errormsg, but do you have the double quotes (see sample below)?

You'll find the name of one of your text controls in the property window's other tab. Select the name and Copy it (CTRL+C), and go to where you have your code:

[tt]Me("NameOfControl&quot.visible = not isnull(Me("NameOfControl&quot)[/tt]

Paste (CTRL+V) your controls name to replace "NameOfControl".

Did you try the second thingie? It should loop thru all textboxes in the report without having to reference them by name individually.

Roy-Vidar

 

[carlosguill]

Thank you Roy, the code for the text boxes worked great, I have a mix of different type of controls, check boxes, combo boxes and dates. If you can give me a hint on how to apply this to all these controls I'll be all set. Thanks again for all the help.
Carlosg

 

[RoyVidar]

Hi again!

Dates normally reside in textboxes, so the textbox thingie should take care of that. But I can't say I'm familiar with using comboboxes on reports.

[tt] dim ctl as control
for each ctl in me.controls
if typeof ctl is textbox or typeof ctl is combobox then
ctl.visible=not isnull(ctl.value)
end if
if typeof ctl is checkbox then
ctl.visible=ctl.value
end if
next ctl[/tt]

This is not tested with comboboxes, but the syntax should be correct...

I'm assuming here that you would like to show the checkboxes only when they are true (checked), they normally cannot have "no value"

Roy-Vidar

 

[carlosguill]

Thank you I'll try this at home tonight, I appretiate all your help.
Carlosg

 

[carlosguill]

Thank you Roy for your suggestion, I had not had time to test it until now, and it worked perfectly.

Carlosg

 

[jet527]

I have a question related to this posting. RoyVidar's code works great for me when they are null values, but how do I rewrite it as to include items that have a value of 0. Some of my values have a default value of 0 for other calculations to work. Any help would be very much appreciated.

 

[RoyVidar]

Hi!

Something like this, perhaps:

[tt] dim ctl as control
for each ctl in me.controls
if typeof ctl is textbox or typeof ctl is combobox then
if isnull(ctl.value) then
ctl.visible=false
elseif isnumeric(ctl.value) then
ctl.visible=ctl.value<>0
else
ctl.visible=true
end if
end if
if typeof ctl is checkbox then
ctl.visible=ctl.value
end if
next ctl[/tt]

Roy-Vidar