Filter Blank Values 1

[samusa]

Could someone tell me how to make labels invisible if value of the field is blank, in a query or Report.

Sam

 

[mp9]

Let's say your label control is called Label1 and your text box control is called Text1. Try this in the OnFormat event of your report's Detail section.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Label1.Visible = (Len(Text1) > 0)
End Sub

If you get problems with nulls you can tinker with the condition and use the IsNull function too, but this should work.

http://www22.brinkster.com/accessory/

 

[stix4t2]

The nz() function will test for null and replace it with a zero length string.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Label1.Visible = (Len([red]nz([/red]Text1,[red]"")[/red]) > 0)
End Sub

 

[PHV]

Another way to test for Null, ZLS or blank:
Label1.Visible = (Trim(Text1 & "") <> "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

This is working fine but when i am converting Report to data access pages report, it doesn't work. Any idea

Sam

 

[fneily]

Samusa - remember to state that you are using a DAP in your question. As you can see, you get good answers for a typical Access form, but it won't work in a DAP. Here's an example of mine that tested a field for an EmployeeID and if it was the right one, make some controls visible. The code went on the onchange event of the input textbox:

If holdID = "Glen" then
Hyperlink1.style.visibility = "visible"
Hyperlink2.style.visibility = "visible"
txtStart.style.visibility = "visible"
txtEnd.style.visibility = "visible"
Text0_Label.style.visibility = "visible"
Text1_Label.style.visibility = "visible"
End If

You would do the opposite, set .style.visibility = "hidden"

If you look at the property sheet of your control, there's an option that says visiblilty. So you can set it visible or hidden.

 

[samusa]

I tried to use following script but it is not working.

<SCRIPT language=vbscript event=onchange for=txt1>
<!--
If bdata = IsNull Then
Label9.style.visibility = "hidden"
Else
Label9.style.visibility = "visible"
End If
-->
</SCRIPT>


Sam

 

[PHV]

Replace this:
If bdata = IsNull Then
with this:
If IsNull(bdata) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

Still not working...

Sam

 

[PHV]

VBScript ? So my favourite should work:
If Trim(bdata & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

Tried it too but not working, generating script errors

Sam

 

[PHV]

Any chance you could post more relevant info like, say, the error message(s) and where in the code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

Here is the code

<SCRIPT language=vbscript event=onchange for=txt1>
<!--
If Trim(bdata &amp; "") = "" Then

Label9.style.visibility = "hidden"
Else
Label9.style.visibility = "visible"
End If
-->
</SCRIPT>

Error : Expected ']'

URL: accdp://158917992/

Sam

 

[PHV]

If Trim(bdata &amp; "") = "" Then
I've never posted that, but this:
If Trim(bdata & "") = "" Then

You're writing VBS code, no HTML data !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

Sorry about that. Actally I copied and pasted directly into Microsoft Script editor. That is why it shows "amp". Anyways Label is still there, did not get invisible

Sam

 

[fneily]

This works: Substitute thistest with your control name

If Len(thistest.value) = 0 then

 

[PHV]

I wonder about the following:
<SCRIPT language=vbscript event=onchange for=[!]txt1[/!]>
If Trim([!]bdata[/!] & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

txt1 is the ID of the control(Text Box). bdata is the Controlsource property of text box.

Sam

 

[PHV]

So, what about this ?
If Trim(txt1.Value & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samusa]

Same result.....

Sam