Recordest Filter(2)

[Maxa]

Sorry guys,

prematuraly posted the thread (Solved: Recordset Filter) as solved.

Thanks to XLD, the code worked well, but another issue arose.

Actually in the range "a5:a23", I have collection of ids, which I want to extract from the recordset.

Lets say the recorset itself looks like this:

123456
234567
345678
........
987456

and the codes in the range look like:
A B
1 123456
2 345678
3 .......
4 987456

by using the code amended by XLD
rst.Filter = "COUNTERPARTYCODE >= " & "a5" & " and " & "COUNTERPARTYCODE <= " & "a23"

I get all the records between the "A1" value and "A4" value above. when actually I want to exclude IDs which are not in a range.


Any help will be very appreciated.

[Bob Phillips]

Can you post an example database and Excel workbook?

[Maxa]

Attachment 9119
Please look in attachments for the spreadsheet.

Sheet 1 containes conditions for the search
Sheet 2 is example of database output

VBA Code:
[vba]
If Worksheets("Sheet2").Range("b3") = "All" Then
Worksheets("Sheet1").Activate
rst.Filter = "COUNTERPARTYCODE >= " & "a4" & " and " & "COUNTERPARTYCODE <= " & "a7"
Else
rst.Filter="COUNTERPARTYCODE = " & Range("b4")

End if

Worksheets("Sheet2").Activate
Range("a6").CopyFromRecordset rst


[/vba]

Range "B3" in Sheet 2 is a key I made, using drop down menu, so I can choose to extract all records specified in conditions (sheet 1) or just single Counterparty Code
The thing is, when I am passing the range, it gives me all records bigger than 2090503 and smaller than 9605631. When actually I want to skip 5608007 and 950006 (marked yellow in spreadsheet)

Would be very grateful for any ideas, bros