Limiting records in a report after grouping

[Berlioz]

Hello, sure glad to find this forum, I have been pulling my hair out trying to figure how to limit the number of records returned in the detail section of a grouped report.

I have a report that has three grouping,
.Track
..Surface
...Distance
Then the actual records are sorted by descending date. This is where I want to limit the records to say the last 12. I can not filter on date because the last twelve could have happened anytime.

I can not figure how to do it, please help.


This is what I tried so far.
I did a little research online and came across a forum that suggested the following
-adding an invisible count box named counter, setting the control source property to =1 and setting the running sum box to across groups.
This added a running count to the detail sections, it resets to zero at every new group.


- then on Format event adding this code
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [Counter] >12 Then Cancel = True
End Sub

I get the count but the report still lists all records. I have never used VBA in the past, maybe I am missing something obvious. Hoping someone guides me in the right direction.

Thank you in Advance

[pbaldy]

Untested, but if the count is right, try setting the visible property of the detail section to False (don't forget an Else clause setting it to True).

[Berlioz]

visible or not visible i have tried it both ways same result it still returns all records.

and Else Clause ? I just copied the code from a forum, where would I add this ?
I sorry to say I am not well versed in VBA, when it comes to this area I need baby instructions.

Thank you

Untested, but if the count is right, try setting the visible property of the detail section to False (don't forget an Else clause setting it to True).

[pbaldy]

In pseudo code:

if Whatever Then
  Visible = False
Else
  Visible = True
End If

[Berlioz]

Following the instructions, I was able to limit the number of records when sent to printer. However what i want to do is limit the amount of records in the report to the last 12 records. I have controls in the group header, I want these controls to only calculate the last 12 records.

Or am I going about this all wrong, do I have a bigger design issue ?
Any ideas ?

In pseudo code:

if Whatever Then
  Visible = False
Else
  Visible = True
End If

[pbaldy]

Have you looked at the TOP predicate in SQL? In other words, the query under the report would look like:

SELECT TOP 12...
FROM...
WHERE...
ORDER BY DateField DESC

[Berlioz]

Using Top in the underlying query returned the top records across all records. I need a TOP records after they have been grouped.

but thanks for the suggestion.

Have you looked at the TOP predicate in SQL? In other words, the query under the report would look like:

SELECT TOP 12...
FROM...
WHERE...
ORDER BY DateField DESC

[pbaldy]

Like this?

http://allenbrowne.com/subquery-01.html#TopN

[Berlioz]

Looks promising.... thank you for the great link.
I'll try it out tomorrow and report back.