Conditional Footer Code

  • Thread starter Thread starter briank
  • Start date Start date
B

briank

I have a text box (Text586) in the Detail Section that is based upon a
dlookup command and shows either a 1 or 0. I would like the text box in the
Report Footer (txt589) to be visible if the value count of Text586 >0. This
report can be run with different parameters that could show the detail
section as one record or 20 records. My code works just only when there is
one record in the report.

If Me.Text586 > 0 Then
Me.Text589.Visible = True
Else
End If
 
You use DLookup() or DCount() or just count() or something in the report
footer.

I generally consider DLookup() or similar in reports a big waste of
resources. There are typically more efficient methods for displaying data.

If you need a more accurate answer, consider telling us something about your
DLookup() and report's record source.
 
Currently the data source is derived from a stored procedure. The Text586 is
a dlookup function residing in my Detail section. I originally tried to use
a text box in my footer with the code =sum([Text586]) but when created my
report would never pop up. I'm assuming that there was a conflict of sorts
that made this difficult. Therefore I thought that utilizing a VBA approach
was a good way of making this work.
 
You can't use
=Sum([A Control Name Here])
It just doesn't work.
If you want to find out about solutions that might work, you need to provide
more significant information about the records in the report's record source
and the source of the other values you want to display in your report.

--
Duane Hookom
MS Access MVP


briank said:
Currently the data source is derived from a stored procedure. The Text586
is
a dlookup function residing in my Detail section. I originally tried to
use
a text box in my footer with the code =sum([Text586]) but when created my
report would never pop up. I'm assuming that there was a conflict of
sorts
that made this difficult. Therefore I thought that utilizing a VBA
approach
was a good way of making this work.

Duane Hookom said:
You use DLookup() or DCount() or just count() or something in the report
footer.

I generally consider DLookup() or similar in reports a big waste of
resources. There are typically more efficient methods for displaying
data.

If you need a more accurate answer, consider telling us something about
your
DLookup() and report's record source.
Click to expand...
Click to expand...
 
The records in the report's record source contains approx 100 - 1500 people
depending on the parameters that the end user picks (there is a pop up menu
that holds a few combo boxes and radio buttons). The fields in the details
are mostly bound while the fields in the footers are mostly unbound (although
some are calculations i.e. sums of the appropriate fields in the detail
section). I have a dlookup text box in the Detail section (alongside bound
fields) that is based upon conditions in another table and this report
(=IIf(DLookUp("[PROV_UHC_CAT_NUM]","[tblMain]","[UHC ID]='" & [Provider ID] &
"' And [FiscalYearInd]=2010")<>DLookUp("[PROV_UHC_CAT_NUM]","[tblMain]","[UHC
ID]='" & [Provider ID] & "' And [FiscalYearInd]=2009"),1,0))

My desired approach is to use VBA to unhide a text box in the footer section
based solely on if the sum of the dlookup across the detail section is >0.
Does this clarify?


Duane Hookom said:
You can't use
=Sum([A Control Name Here])
It just doesn't work.
If you want to find out about solutions that might work, you need to provide
more significant information about the records in the report's record source
and the source of the other values you want to display in your report.

--
Duane Hookom
MS Access MVP


briank said:
Currently the data source is derived from a stored procedure. The Text586
is
a dlookup function residing in my Detail section. I originally tried to
use
a text box in my footer with the code =sum([Text586]) but when created my
report would never pop up. I'm assuming that there was a conflict of
sorts
that made this difficult. Therefore I thought that utilizing a VBA
approach
was a good way of making this work.

Duane Hookom said:
You use DLookup() or DCount() or just count() or something in the report
footer.

I generally consider DLookup() or similar in reports a big waste of
resources. There are typically more efficient methods for displaying
data.

If you need a more accurate answer, consider telling us something about
your
DLookup() and report's record source.

--
Duane Hookom
Microsoft Access MVP


:

I have a text box (Text586) in the Detail Section that is based upon a
dlookup command and shows either a 1 or 0. I would like the text box
in the
Report Footer (txt589) to be visible if the value count of Text586 >0.
This
report can be run with different parameters that could show the detail
section as one record or 20 records. My code works just only when
there is
one record in the report.

If Me.Text586 > 0 Then
Me.Text589.Visible = True
Else
End If
Click to expand...
Click to expand...
Click to expand...
 
Just a guess but I expect you could create a crosstab query based on tblMain
that has [UHC ID] field as the Row Heading, "Yr" & FiscalYearInd as the
Column Heading, and Max(PROV_UHC_CAT_NUM) as the value. Then add this
crosstab to the Record Source query of your report and join the [Provider
ID] field to [UHC ID]. You can then just compary [Yr2010] and [Yr2009].

--
Duane Hookom
MS Access MVP


briank said:
The records in the report's record source contains approx 100 - 1500
people
depending on the parameters that the end user picks (there is a pop up
menu
that holds a few combo boxes and radio buttons). The fields in the
details
are mostly bound while the fields in the footers are mostly unbound
(although
some are calculations i.e. sums of the appropriate fields in the detail
section). I have a dlookup text box in the Detail section (alongside bound
fields) that is based upon conditions in another table and this report
(=IIf(DLookUp("[PROV_UHC_CAT_NUM]","[tblMain]","[UHC ID]='" & [Provider
ID] &
"' And
[FiscalYearInd]=2010")<>DLookUp("[PROV_UHC_CAT_NUM]","[tblMain]","[UHC
ID]='" & [Provider ID] & "' And [FiscalYearInd]=2009"),1,0))

My desired approach is to use VBA to unhide a text box in the footer
section
based solely on if the sum of the dlookup across the detail section is >0.
Does this clarify?


Duane Hookom said:
You can't use
=Sum([A Control Name Here])
It just doesn't work.
If you want to find out about solutions that might work, you need to
provide
more significant information about the records in the report's record
source
and the source of the other values you want to display in your report.

--
Duane Hookom
MS Access MVP


briank said:
Currently the data source is derived from a stored procedure. The
Text586
is
a dlookup function residing in my Detail section. I originally tried
to
use
a text box in my footer with the code =sum([Text586]) but when created
my
report would never pop up. I'm assuming that there was a conflict of
sorts
that made this difficult. Therefore I thought that utilizing a VBA
approach
was a good way of making this work.

:

You use DLookup() or DCount() or just count() or something in the
report
footer.

I generally consider DLookup() or similar in reports a big waste of
resources. There are typically more efficient methods for displaying
data.

If you need a more accurate answer, consider telling us something
about
your
DLookup() and report's record source.

--
Duane Hookom
Microsoft Access MVP


:

I have a text box (Text586) in the Detail Section that is based upon
a
dlookup command and shows either a 1 or 0. I would like the text
box
in the
Report Footer (txt589) to be visible if the value count of Text586
0.
This
report can be run with different parameters that could show the
detail
section as one record or 20 records. My code works just only when
there is
one record in the report.

If Me.Text586 > 0 Then
Me.Text589.Visible = True
Else
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top