How to Filter multiple info in a query using a macro???

[Hebler]

Just learning macros, the ApplyFilter Expression Builder only allows a certain amount of characters to be entered to detail what you need to have filtered, or in this case, filtered out. My problem is this: I have to filter out 88 different catagories in a field. The field now has 200+ different catagories. My goal is to have those remaining 122+ catagories available in a report. When I create a macro using "<>" or "Not Like" in the Builder, I run out of room and have to end that expression. If I try to apply another filter to continue afterwards, it filters out only the new information, but the previous catagories that I needed filtered out in the first Expression, return. How do I filter out 88 different catagories in a query's field using a macro? Can it be done? If not, is Visual Basic the way to go? And not knowing too much about Visual Basic, how do you do it? Please help.

 

[elizabeth]

Assuming your 122+ categories are in a lookup table, add a yes/no field to the table called something like "Include". Fill it in manually and from then on you can use it as the filter criteria.

 

[DougP]

Well first of all given the amount of fields you are using.<br>
I think you are hitting the limits of Access.<br>
And I don't know if VB will extend that limit or not.<br>
are you using Access '97 or 2000?<br>
<br>
But Yes VB is very powerful and if it will work here are basics <br>
<br>
first in VBA you have to open the recordset<br>
this is done by DIMesioning it<br>
then SETting it to something<br>
-----------------------------<br>
Dim db as database, rst as recordset, SQL as string<br>
Set db = CurrentDb<br>
' SQL string.<br>
SQL = &quot;SELECT * FROM Orders WHERE OrderDate &gt;= #1-1-95#;&quot;<br>
Set rst = db.OpenRecordset(SQL)<br>
MyValue = rst!OrderDate <br>
----------------------------------<br>
OK<br>
the SQL statement is where your fields will go<br>
<br>
then set it<br>
<br>
Now your results come back in the &quot;rst&quot; part<br>
as soon as you set it the results are all in ht ethree letter word<br>
so to get somehing meaningfull out of it<br>
Look at the MyValue line.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>