asked on

Find many none adjacent records

I have an Access 10 db with many tables. One of which contains over 20000 records with a long int PK called OrderID. The user needs to find up to 10 of those records and view them via an continuous Access Form. These records are not close to each other so scrolling is a no no. I thought the "IN" function within an Access query would do the trick. The question is how do I pass a variable list of OrderID's in to a IN function within a query, eg IN(20223,19001,12544)?

regards
cornholme

Rey Obrero (Capricorn1)

here is a sample query

select *
from tableName
where [OrderID] In(20223,19001,12544)

cornholme

ASKER

Thanks Rey,

But that's hard coded in to the query. I need to have the OrderID's as a variable so it can run many times with different values and different number of values.
Sorry if did not that clear.

cornholme

PatHartman

You can change the report's RecordSource on the fly. This is an example from a report where the "in" criteria is optional. It looks at a hidden control on the calling form where the In clause was built.

Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

    If Forms!frmVariables!txtSelectedPrefixes & "" = "" Then
    Else
        strSQL = "SELECT * FROM " & Forms!frmVariables!txtExcelQueryName & " WHERE "
        strSQL = strSQL & " DrawingPfx In (" & Forms!frmVariables!txtSelectedPrefixes & ") OR forms!frmVariables!txtSelectedPrefixes is null;"
    
        Me.RecordSource = strSQL
    End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

You don't say whether your data is "Access" or SQL Server. Assuming it is "Access", it almost doesn't matter what technique you use. However, if you are using SQL Server or think you might want to upsize at one point, I would use better client/server techniques that reduce the number of rows returned from the server. By using filter, you are asking the query engine to bring down all the records from the server and the filtering happens locally. By using queries with criteria, the server does the filtering and only the requested records are downloaded - much better for network performance to keep the traffic to a minimum. Given that with "Access" data, it doesn't matter but with SQL Server (or other RDBMS) it does, I always use the preferred client/server technique. That way I have much less work to do if I need to upsize.

cornholme

ASKER

Hi Pat
Thank you for your observations. In this case its an Access frontend with an Access backend. I will research using the query approach you suggested.

regards
cornholme