Conditional Footer Code
in [Reports]
|
From: briank on 7 Apr 2010 13:51 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
From: Duane Hookom on 7 Apr 2010 14:10 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
From: briank on 7 Apr 2010 15:17 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
From: Duane Hookom on 7 Apr 2010 15:42 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" <briank(a)discussions.microsoft.com> wrote in message news:A564BD53-D22B-4FDF-80CC-3D024A8A43E2(a)microsoft.com... > 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
From: briank on 7 Apr 2010 16:06 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" <briank(a)discussions.microsoft.com> wrote in message > news:A564BD53-D22B-4FDF-80CC-3D024A8A43E2(a)microsoft.com... > > 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 >
|
Next
|
Last
Pages: 1 2 Prev: multiple selection in report Next: How do I open my report in print preview mode in Access? |