can the report refilter of only the top 20 of each group in the field?

  • Thread starter Thread starter ysop
  • Start date Start date
Y

ysop

How ?
To make the report display only the top 20 of each of all
categories in that value field. And, Yes at the footer
I 'd like to have the sum of value of not the top 20 but
to all items in those categories.
 
You can add a text box in the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the detail section On Format event
Cancel = Me.txtCount >20
 
So , it's success only first part.
But on the Detail Section on Format to put
cancel = me.txtcount>20
has referred to the error of no "cancel" function.
and object "Me" not found.
 
Did you place the line I provided in the code behind the report? I should
have been more clear that this can't be placed in the event property.
Your code should look similar to:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.txtCount >20
End Sub
 
Thanks it's work now.
But another thing is I'd like to do is the sum of the
value of only 20 items in each category together with the
sum of all in the footer. I'd like to have both of them to
show.
 
What have you tried. I wouldn't know exactly how to do this without
re-creating your report and playing with it for a while to see what happens.
I don't feel right about taking time to do this without you first seeing how
far you get.

If you can't find a solution, come back with what you tried and your
results.
 
Thanks for your advice

But the result of the sum of the value of each category
for after the refilter on only 20 items not shown for. So
the asking to making the sum of the value at the footer
shown to sum total of all items, but it's fine at the
first place. In doing so, the compare result of the top 20
and the all items is needed to do here at the footer. So
if I was to begin making the new report the result was the
same as to shown only the sum value of all items not with
the value of sum of only 20 items.
 
Thanks for helps

so the more clear thing, that is the sum value of the
detail shown on the footer is the sum value of all items
not the sum value of txtcount > 20, its sum value is 120
or that all items in that category. When I 've pressed
the "analyze by excel" the sum value back to the sum of
not more 20 or according to the actual items shown which
is really contradicted to the ACCESS sum value. So what to
do is I like to have the solving problem of this by
showing both sum value of all as the txtcount do none
effect and the sum value of only 20 as the txtcount do the
effect.
 
And you have tried what? You may need to do some SQL modifications. If you
need further assistance, you should share your table(s) structure.
 
hoo::
I have tried so much but the only change was made is only
on the format. It's okey. If you could do some more, I put
the txtcount on the footer and want to show the last value
as the detail section only the last sum(not sum all).
 
To total only the visible values assuming you want to sum a field named
[Qty].
- you have a text box [txtCount] that numbers the detail section
- Add a text box in the detail section
Name: txtRunSumQty
Control Source: =Abs([txtCount]<=2)*[Quantity]
Running Sum: Over Group
Visible: No
-Add a text box to the Group Footer section
Control Source: =txtRunSumQty
 
Back
Top