Passing multiple criteria to query a table

  • Thread starter Thread starter MarMo
  • Start date Start date
M

MarMo

Hello,
I have a table with clientnumbers and SUBclientnumbers.
client SUBcl
880030 100
880030 101
880030 102
880040 206
880040 207
880040 208
I need to pass the SUBclientnbrs to a query to filter a table.
Client 880030 has SUBcl 100 , 101, 102. How can i pass these criteria
to a query as
SELECT * FROM clients WHERE [CLNBR] = 100 , 101, 102
I'm getting the subcl with VBA code from the table by recordset.

Thanks for helping me out.
Mario
 
You could do this several ways:

SELECT * FROM Clients WHERE [CLNBR] IN (100, 101, 102)

OR

SELECT * FROM Clients
WHERE [CLNBR] = 100
OR [CLNBR] = 101
OR [CLNBR] = 102

How are you using this query? Is it the recordsource for a form? If so,
you might not want to do the filtering in the query, but use the forms Filter
and FilterOn properties.

Filter: "[CLNBR] IN (100, 101, 102):
FilterOn: True
 
Hi Dale ,
Thanks for responding so quickly.
it's a little complicated.

I get a file with all orders for a month.(between 20000 to 40000 recs)
for ALL clients
This file is imported into Access.(TblInvoicedOrders)
For several clients i need to mail the orders (in a textfile) , but
some clients have multiple subclientnbrs.
So , i need to filter or query out the orders matching all the
subclientnbrs for 1 client.
I have set up 4 tables (Clients - Emails - ExtractGroup -
ExtrClientNbrs) to get the emails and subclientnbrs.
Each client can have multiple emailaddresses and multiple
subclientnbrs.
The mailaddreses are not the problem. They will be passed when i setup
the email.
My problem is to get all data matching the criteria (multiple
subclientnbrs) and extracting this data.
I was thinking about an array to put the criteria in , but i'm a
little stuck.
This is part of the code i use now.

'********* passing the clientnbr (intCLNR) to get a recordset with
only the selected clientnumber.
strEXTRACTCLNR = "SELECT * FROM TblGrpNbrs WHERE [ExtrGrpNr]=" &
intCLNR & ""
Set dbExtractNBR = CurrentDb()
Set rstExtractNBR = dbExtractNBR.OpenRecordset(strEXTRACTCLNR,
dbOpenDynaset)
With rstExtractNBR
.MoveLast
intExtrCNTR
= .RecordCount
.MoveFirst
While Not .EOF
If intExtrCNTR
1 Then
Click to expand...
strSQLExtrCLNR
= strSQLExtrCLNR & "," & ![ExtrKlantNr]
Else
strSQLExtrCLNR
= ![ExtrKlantNr]
End If
.MoveNext
Wend
strSQLInvoices =
"SELECT * FROM QryInvoices WHERE [KlantNr] IN(" & strSQLExtrCLNR & ")"

it's not finished yet , i still need to do modifications.
Mario
 
Back
Top