If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Footer Code
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 |
#2
|
|||
|
|||
Conditional Footer Code
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 "briank" wrote: 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 |
#3
|
|||
|
|||
Conditional Footer Code
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" wrote: 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 "briank" wrote: 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 |
#4
|
|||
|
|||
Conditional Footer Code
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" wrote in message ... 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" wrote: 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 "briank" wrote: 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 |
#5
|
|||
|
|||
Conditional Footer Code
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" wrote: 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" wrote in message ... 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" wrote: 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 "briank" wrote: 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 |
#6
|
|||
|
|||
Conditional Footer Code
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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 "briank" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|