Top_N_Value Query-Report (2000/2002)

Topic

New Reply

Is there a way to change the TopValue value in a Top_N_Value Query or Report using a Dialog Form.

Something like: From the Dialog Form I select a Supplier, Date Range, TopValue=5 at this time, and later the user may want to see TopValue=15

Reply | Quote

Replies

Sort the report the way you want in the Sorting and Grouping window.

Put a text box in the detail section of the report.
Set its Control Source to =1
Set its Running Sum property to Over all.
Set its name to txtCounter.
Create the following On Format event procedure for the Detail section of the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.txtCounter > Val(Forms![frmDialog]![txtTopN]))
End Sub

where frmDialog is the name of the dialog form, and txtTopN is the name of the text box (or combo box, or list box) in which the user specifies the number of records to be displayed.

You may want to leave txtCounter visible during testing, and hide it by setting Visible to No in the definitive version.

Reply | Quote