How do I hide fields in a report that have no values?

[djdeuph]

Hi, I am creating a report where under a column with a header titled people, I am pulling in information from 3 fields in my query. I have actually sized the control so that each field would come on a new line. However, occasionally one of the fields for a given record will not have data. I would like to add some sort of If statement that checks this and moves the other lines up rather than leaving a space. I tried the following which doesn't work:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me!txtFacilitator.Value > "" Then
Me!txtFacilitator.Visible = True
Else: Me!txtFacilitator.Visible = False
End If

If Me!txtBA.Text > "" Then
Me!txtBA.Visible = True
Else: Me!txtBA.Visible = False
End If

If Me!txtMedicaid.Text > "" Then
Me!txtMedicaid.Visible = True
Else: Me!txtMedicaid.Visible = False
End If

If Me!txtOther.Text > "" Then
Me!txtOther.Visible = True
Else: Me!txtOther.Visible = False
End If
End Sub

Thanks in advance for your help!

Derek Danilson

 

[rob9740]

Hi Derek,

Have a look at the properties of the text box if you set the can shrink and can grow properties to yes it should make the text box disappear automatically if there is no value in it. Let me know if it works for you.

Rob

 

[djdeuph]

Hi Rob,

Yes, I actually tried that first before going the code route. So, maybe that implies that my query isn't returning a null value but in fact returning spaces? Any other ideas would be great! Thanks!

Derek

 

[Guest_imported]

Hi

I have a simialr probelm with my report, where I have several fileds in my table with different codes. The query is a crosstab and it will count the amount of codes for each day in a ccrosstab view. The report is setup with all of the codes next to each other ready for the numbers however, when a complaint code is not in the table it will say "Does not recognise "codename" as a field or value". I know this does this because it is null but how can I get it to return a "0" if it is null?? It will not run at all if there is no value for each code...

Any help much appreciated

Cheers
Jon

 

[rob9740]

Hi Derek,
If you run the query on its own can you see if any of the values are null or have a space in them?
For using the code version i would use the isnull() property rather than checking for an empty string. So your code would look something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If isnull(Me!txtFacilitator.Value) Then
Me!txtFacilitator.Visible = True
Else: Me!txtFacilitator.Visible = False
End If

If isnull(Me!txtBA.Text) Then
Me!txtBA.Visible = True
Else: Me!txtBA.Visible = False
End If

If isnull(Me!txtMedicaid.Text) Then
Me!txtMedicaid.Visible = True
Else: Me!txtMedicaid.Visible = False
End If

If isnull(Me!txtOther.Text) Then
Me!txtOther.Visible = True
Else: Me!txtOther.Visible = False
End If
End Sub

I had tried checking for empty strings before as you were doing but i wasn't getting the results i should have but the isnull() property worked. Givw it a go and see.

Jon: try using the isnull() property and see if it makes a difference for you.I'm not to familiar with crosstab queries so i'm not to sure how it will work for you.

Rob