Report: Show Fields Only if Populated

  • Thread starter Thread starter Stephen Lynch
  • Start date Start date
S

Stephen Lynch

I am trying to figure out how to do this. I have 10 fields. When I run a
report maybe only 3 fields at a given time will be populated with data.

For example: Report 1 has Fields A, C, F populated so I want my report to
show:

Lable: Deferrals Match Roth
Data: A C F

On the next run, the report may have Fields C, F, H populated so My report
should have

Lable: Match Roth Rollover
Data: C F H

I am trying to show only information that is useful on the report not the
data that I do not need. I know that I can send the data to a sperate table
with just the populated fields but I need to have the lable text of each
field showing correctly on the report.

Any ideas on how to get started with this?

Thanks in Advance


Steve
 
Hi Steve,

Here is one way, probably subject to refinement. Create a hidden text
box for each possible field and bind them accordingly. Name them something
like txtItem1, txtItem2, txtItem3, .... Place the text for their labels in
their Tag properties. Create enough visible, unbound boxes as you need.
Also, create enough labels as you need. Name them something like lblItem1,
lblItem2, lblItem3, ... and txtDisplay1, txtDisplay2, txtDisplay3, .... In
your detail's on format event do something like this (untested:

Const cintFieldCount As Integer = the total number of fields
Dim intIndex1 As Integer
Dim intIndex2 As Integer
Dim txtCurrent As TextBox

intIndex2 = 1
For intIndex1 = 1 To cintFieldCount
Set txtCurrent = Controls("txtItem" & intIndex1)
If Not IsNull(txtCurrent.Value) Then
Controls("lblItem" & intIndex2).Caption = txtCurrent.Tag
Controls("txtDisplay" & intIndex2).Value = txtCurrent.Value
intIndex2 = intIndex2 + 1
End If
Next intIndex1
For intIndex1 = intIndex2 To cintFieldCount
Controls("lblItem" & intIndex1).Caption = Null ' Or maybe set to ""
Controls("txtDisplay" & intIndex1).Value = Null
Next intIndex1

Hope that helps,

Clifford Bass
 
Thanks Cliff;

I will give it a shot.

Clifford Bass said:
Hi Steve,

Here is one way, probably subject to refinement. Create a hidden text
box for each possible field and bind them accordingly. Name them
something
like txtItem1, txtItem2, txtItem3, .... Place the text for their labels
in
their Tag properties. Create enough visible, unbound boxes as you need.
Also, create enough labels as you need. Name them something like
lblItem1,
lblItem2, lblItem3, ... and txtDisplay1, txtDisplay2, txtDisplay3, ....
In
your detail's on format event do something like this (untested:

Const cintFieldCount As Integer = the total number of fields
Dim intIndex1 As Integer
Dim intIndex2 As Integer
Dim txtCurrent As TextBox

intIndex2 = 1
For intIndex1 = 1 To cintFieldCount
Set txtCurrent = Controls("txtItem" & intIndex1)
If Not IsNull(txtCurrent.Value) Then
Controls("lblItem" & intIndex2).Caption = txtCurrent.Tag
Controls("txtDisplay" & intIndex2).Value = txtCurrent.Value
intIndex2 = intIndex2 + 1
End If
Next intIndex1
For intIndex1 = intIndex2 To cintFieldCount
Controls("lblItem" & intIndex1).Caption = Null ' Or maybe set to ""
Controls("txtDisplay" & intIndex1).Value = Null
Next intIndex1

Hope that helps,

Clifford Bass
Click to expand...
 
Back
Top