Hide null values

[Niall]

I would like to know if it is possible to hide Fields with
Null Values. For example I may have a report that can have
up to ten fields; however, I may not want to show all of
the fields if they have Null values. Please keep in mind
that I do not want to hide RECORDS with NULL VALUES.

I am using the following code on the OnFormat event:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
On Error GoTo err_Detail_Format

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If ctl.Text = "" Or IsNull(ctl.Text) Then
With ctl
..Visible = False
End With
Else
With ctl
..Visible = True
End With
End If
End If
Next ctl


exit_Detail_Format:
Exit Sub

err_Detail_Format:
MsgBox Err.Description
Resume exit_Detail_Format

End Sub

however this is only providing limited success

 

[Allen Browne]

It should not be necessary to hide them: just set their CanShrink property
to No.

If they have attached labels and you want to get rid of them also,
right-click the label and Change To | Text Box. Set the ControlSource of the
new text box to something like this:
=IIf(IsNull([Surname]), Null, "Surname")

 

[Guest]

Thanks that works perfectly unfortunatley some of the
fields are combo boxes and when i make these into text
boxes and tell it to look at the same field i get the key
value for that field and not the actual result i am
looking for.

-----Original Message-----
It should not be necessary to hide them: just set their CanShrink property
to No.

If they have attached labels and you want to get rid of them also,
right-click the label and Change To | Text Box. Set the ControlSource of the
new text box to something like this:
=IIf(IsNull([Surname]), Null, "Surname")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like to know if it is possible to hide Fields with
Null Values. For example I may have a report that can have
up to ten fields; however, I may not want to show all of
the fields if they have Null values. Please keep in mind
that I do not want to hide RECORDS with NULL VALUES.

I am using the following code on the OnFormat event:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
On Error GoTo err_Detail_Format

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If ctl.Text = "" Or IsNull(ctl.Text) Then
With ctl
.Visible = False
End With
Else
With ctl
.Visible = True
End With
End If
End If
Next ctl


exit_Detail_Format:
Exit Sub

err_Detail_Format:
MsgBox Err.Description
Resume exit_Detail_Format

End Sub

however this is only providing limited success

.

 

[Allen Browne]

Use a query as the source for the report.

Include the lookup tables in the query, so you can use the lookup fields
instead of the foreign key value.

If some of the foreign key values are Null, you will need to use outer joins
so you get all the records. More info on that:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks that works perfectly unfortunatley some of the
fields are combo boxes and when i make these into text
boxes and tell it to look at the same field i get the key
value for that field and not the actual result i am
looking for.

-----Original Message-----
It should not be necessary to hide them: just set their CanShrink property
to No.

If they have attached labels and you want to get rid of them also,
right-click the label and Change To | Text Box. Set the ControlSource of the
new text box to something like this:
=IIf(IsNull([Surname]), Null, "Surname")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I would like to know if it is possible to hide Fields with
Null Values. For example I may have a report that can have
up to ten fields; however, I may not want to show all of
the fields if they have Null values. Please keep in mind
that I do not want to hide RECORDS with NULL VALUES.

I am using the following code on the OnFormat event:

Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
On Error GoTo err_Detail_Format

Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is TextBox Then
If ctl.Text = "" Or IsNull(ctl.Text) Then
With ctl
.Visible = False
End With
Else
With ctl
.Visible = True
End With
End If
End If
Next ctl


exit_Detail_Format:
Exit Sub

err_Detail_Format:
MsgBox Err.Description
Resume exit_Detail_Format

End Sub

however this is only providing limited success