Restrict report to first x records where x is user specified

[Robyn_P]

Hey, I'm looking for a way to restrict the report to just show the first x records where x is a number defined by the user. I can do this by getting the recordset and looping through it getting the ID of each record and building a WHERE statement that way, but this seems inefficient. Can anyone suggest another way?

[June7]

I have user select beginning and ending values and use BETWEEN AND for filtering on the range. Or you can calculate the ending value from the given beginning.

You could consider code that sets the report RecordSource with a SELECT TOP N sql statement but this gets rather complicated. Unfortunately the N value for a TOP N query cannot be a dynamic parameter and that's why code would have to set the RecordSource property.

[Robyn_P]

Ooh BETWEEN could work. I looked at the SELET TOP N but as you said, it looked complicated. Will try you're BETWEEN solution and report back. Thanks

[Robyn_P]

Beautiful. I did this:
' Build strWhere for the most recent x records
Set OIs = CurrentDb.OpenRecordset("SELECT * FROM tblOpenItems ORDER BY [ID] DESC")

' Get the first ID
OIs.MoveFirst
ID1 = OIs("ID")

' Move to the nth record and get the ID
OIs.Move Me.txtQuantity - 1
ID2 = OIs("ID")

' Build the strWhere
strWhere = "[ID] BETWEEN " & ID1 & " AND " & ID2

[isladogs]

For future reference, if it was an action query such as a make table query, you could do something like this

Dim N= Integer

'N = some value set in code or on form control

CurrentDB.Execute "SELECT TOP " & N & " .... INTO .... FROM ... WHERE ...."

[Robyn_P]

I thought about SELECT TOP N but I wasn't sure how I would then use that when opening the report. How would you then go on to open the report?

[isladogs]

I thought about SELECT TOP N but I wasn't sure how I would then use that when opening the report. How would you then go on to open the report?

First of all it would be "SELECT TOP " & N & " ....

I specifically said this approach was for action queries

I've not tested the following and it may well not work for TOP N or give an error
However I use something similar for other reports where the record source may vary e.g. based on crosstab query

a) Set the value of N in a textbox in the calling form e.g. N=5

Then set the record source for the report

strSQL = "SELECT TOP " & N & " ...."
DoCmd.OpenReport "MyReportName", avViewPreview

In your report, add this code

Private Sub Report_Open()    
Me.RecordSource = strSQL
End Sub

By all means try it but don't be surprised if it errors

[Robyn_P]

Thanks for the explanation. I'll stick with the solution I've used above. The calling form has lots of different options for filtering the report so it's simpler just to build the strWhere.