Can't get DMax to work

[Crystee24]

Hi all. I have a report built with a subreport that has many calculated
fields. I built the calculated fields on the subreport. I need to place a
summary at the bottom of the report that gives the min and max values of some
of the calculated fields in the subreport. For Exa:
Subreport fields:
VStrike (calculated field)
V50 (text field)

I need the max number from the VStrike field where V50='Y'. I have tried just
about everything I can think of. I keep getting #error. Is there a way to do
this without building queries for each of my calculated fields? Any help
would be great.

 

[Larry Linson]

Hi all. I have a report built with a subreport
that has many calculated fields. I built the
calculated fields on the subreport. I need to
place a summary at the bottom of the report
that gives the min and max values of some
of the calculated fields in the subreport. For
Exa: Subreport fields:
VStrike (calculated field)
V50 (text field)

I need the max number from the VStrike field
where V50='Y'. I have tried just about everything
I can think of. I keep getting #error. Is there a
way to do this without building queries for each
of my calculated fields? Any help
would be great.

If your question is, "Can the DMax domain aggregate function return the
Maximum for more than a single Field?", the answer is "No."

If your question is, "Is there an alternative to using DMax to fill the
Calculated Controls in the Report Footer?" then my answer would be, I can
without putting much effort into it, think of two:

1. In each record where you use the calculated Field,
compare it against a Report-level variable, and save
the new value if it is larger than the saved value (if
going for Max). Use the Print event as the home for
this VBA code.

2. In the Report footer, in VBA code in the Print event,
execute a Totals Query that performs the calculations
and selections you want, and store those values in
unbound Text Boxes in the Report Footer.

Larry Linson
Microsoft Access MVP

 

[Crystee24 via AccessMonster.com]

Hi all. I have a report built with a subreport
that has many calculated fields. I built the

[quoted text clipped - 12 lines]

of my calculated fields? Any help
would be great.

If your question is, "Can the DMax domain aggregate function return the
Maximum for more than a single Field?", the answer is "No."

If your question is, "Is there an alternative to using DMax to fill the
Calculated Controls in the Report Footer?" then my answer would be, I can
without putting much effort into it, think of two:

1. In each record where you use the calculated Field,
compare it against a Report-level variable, and save
the new value if it is larger than the saved value (if
going for Max). Use the Print event as the home for
this VBA code.

2. In the Report footer, in VBA code in the Print event,
execute a Totals Query that performs the calculations
and selections you want, and store those values in
unbound Text Boxes in the Report Footer.

Larry Linson
Microsoft Access MVP

Thanks Larry for your help. I am just learning VB and Im not really sure
where to go with this. Do you have any examples? I could post my DB for you
to look at but I don't see any place to do that.