Max Number of Records Returned by a Query Based on a Form-Entered Value

[Karen D]

Help Again!! I’m using Access 2003 and I have a form that allows users to enter criteria for selecting tables and queries as well as the query parameters that will be used to generate a report. The report includes some minor but essential statistical information in its summary. One of the parameters I need to allow the user to provide is the maximum number of records to include in the report’s analysis (e.g., should the report include 30, 45, 52, etc. of the most recent records?). The Top Value property in the query would be perfect if I could get it to read a variable name instead of a fixed integer value. I’ve tried the MaxRecord property as well as assigning row numbers in the query and neither approach has worked (my sort field may have duplicates, my unique identifier doesn’t provide a record count in the right order). I know there should be a simple solution to this but I can’t find it. Is there a way to limit the number of records returned by a query based on a value entered into a form? Thanks!

[ADezii]

1. Create a Query that includes ALL Records from your Data Source(s).
2. Modify the SQL Property of the Query, reading the TOP Number of Values from a Text Box, in this case txtTOP.
3. Control which Records appear by the ORDER BY Clause.
4. Open the Query, remembering that Ties will also be included in the Result Set if relevant.
5. In the following Demo, based on the [Order Details] Table of the Northwind Sample Database, I will display the TOP N Records, when N is a Value in the Text Box txtTOP on the Current Form.
6. The Records will reflect the TOP N Records based on [UnitPrice] DESC as indicated in the ORDER BY Clause in Line #10.
   

   Dim qdf As QueryDef
   Dim strSQL As String
    
   'Make sure there is a Value in the Text Box txtTOP, it is Numeric, and it is < 1000
   If IsNull(Me![txtTOP]) Or Not IsNumeric(Me![txtTOP]) Or Me![txtTOP] >= 1000 Then Exit Sub
    
   'Customize the SQL for your Query reading the TOP Values from txtTOP
   strSQL = "SELECT TOP " & Me![txtTOP] & " [Order Details].OrderID, [Order Details].ProductID, " & _
            "[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount " & _
            "FROM [Order Details] ORDER BY [UnitPrice] DESC;"
    
   Set qdf = CurrentDb.QueryDefs("qryOrderDetails")
    
   qdf.SQL = strSQL
    
   DoCmd.OpenQuery "qryOrderDetails", acViewNormal, acReadOnly
   DoCmd.Maximize