Varying number of records

D

Dave

I have to produce a number of reports identifying the top xx number of
problems that occure with specific items.

I have produced a report from a query which will filter and display the top
results for each item.

The problem is that sometimes I need to show the top 10 issues, other times
the top 20, 30 or 50 issues.

Is there a way of automating the number of records retreived by the query or
displayed by the report which doesn't require the user going into the 'Top
values' option in the query.
 
A

Allen Browne

To supply the top xx issues per group, you would generally use a subreport.
The subreport will be based on a query using "SELECT TOP 20 ..."

If that's what is going on, you can reassign the SQL property of the
subreport's query before you OpenReport. Typicallly this means using a form
where the user enters the number of items to show, and clicks a button to
open the report. The button's Click event can re-write the SQL property of
the query so it shows the desired number.

This example assumes a text box named txtTopNum. Set its Format property to
something like General Number so the user can't enter a non-numeric value.
Example:

Private Sub cmdPrint_Click()
Dim strSql As String
If IsNull(Me.txtTopNum) Then
strSQL = "SELECT
Else
strSQL = "SELECT TOP " & Me.txtTopNum
End If
strSQL = strSQL & " * FROM MySubTable;"
CurrentDb().QueryDefs("MySubReportQuery").SQL = strSQL

DoCmd.OpenReport "MyReport", acViewPreview
End Sub
 
Top