Limiting the number of records in a report

[Guest]

How do I limit the number of detail records in an Access report? The report
is sorted by several fields, but I want only 50 records for the first sort
field.

 

[John Vinson]

How do I limit the number of detail records in an Access report? The report
is sorted by several fields, but I want only 50 records for the first sort
field.

Base the Report on a query and set the query's Top Values property
(right mouseclick the grey background of the tables and select
Properties) to 50.

John W. Vinson[MVP]

 

[strive4peace]

Go to the design view of the report

turn on the properties window
(from the menu --> View, Properties)

select the report itself
(click in the upper left where the rulers intersect)

the titlebar of the properties window will say "Form"

in the properties window, click on the DATA tab

click in the RecordSource property

click on the Builder (...) button to the right

now, you are on a screen that looks like a query

turn on the properties
(View, properties from the menu)

click in a gray area that is not a fieldlist or a column

the titlebar of the properties window will say "Query
Properties"

click in the TOP VALUES property and enter 50
(you can enter a specif number or a percentage)

from the File menu, choose close
and then Yes to update the SQL

save your form

Voila!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Thanks, but I should have given more detial on my question... The query on
which the report is based is used for multiple other reports, which should
not be limited, so I cannot limit records at the query level. Also, I want
to limt the records to a set number in each group, not a total for the
report. One of the categories the report is grouped on is "urgency value".
I want only the top 50 records in each urgency value group to appear on the
report.

Is it possible to do this at the report level, or do I have to create a
separate query and report for each urgency group?

Many thanks,

dsharbaugh

 

[strive4peace]

Hi

put this control on your report in the detail section:

then, in the Detail OnFormat event -->

textbox:
Name --> CountRows
ControlSource --> =1
RunningSum --> Overall

'~~~~~~~~~~~~
Private Sub Detail_Format( _
Cancel As Integer, _
FormatCount As Integer)

If Me.CountRows > 50 Then
Me.Detail.Visible = False
End If
End Sub
'~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*