Prev: multiple selection in report
Next: How do I open my report in print preview mode in Access?
From: Duane Hookom on 8 Apr 2010 00:31
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" <briank(a)discussions.microsoft.com> wrote in message
news:9CD4EDD2-FC9F-4D63-9221-DF331908E2DE(a)microsoft.com...
> 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
>>
First  |  Prev  | 
Pages: 1 2
Prev: multiple selection in report
Next: How do I open my report in print preview mode in Access?