Multiple Choices for Parameters with VBA and Queries

[weasel7711]

Hello
I am working on a database for my family's small business. We have a small database ~2000 records in the main table. We repair small industrial production machines and they are shipped in from our customers across the globe. I am new to VBA programming, but I have an academic background in C++ so I understand basic to advanced programming concepts, as well as OOP.

We are replacing our white-board with our database. So to make the transition easier for our less computer savvy employees I have developed a form called the "whiteboard" which will visually represent the same data that they previously wrote/erased on the old whiteboard. So I have a button that runs a set query of our priority units/repairs.

The problem lies in a "power search" that I am developing which will be able to search our DB by specific or a range of criteria in multiple fields. Very very similar to the way that NewEgg.com does their power searches, in fact that is where I got the idea.

So I know I can make it work by adding a bunch of if/else statements and going through every possible scenario of combinations, but that will obviously add a huge amount of overhead to running the query. So I need to do it more productively.

My question is: With VBA is there an object property of queries that allows me to maybe pass a list of criteria that can be dynamically changed each time a user changes the search criteria. Maybe pass something

So the SQL would look something like (VERY SIMPLY):

SELECT * FROM tblRMA
WHERE
tblRMA.Status IN ("Completed", "Evaluation in Progress", "Shipped")
AND
tblRMA.Customer IN (Customer_A, Customer_S, Customer_X, Customer_Y)
AND
...
AND
...

but if they chose different parameters and clicked search again it would be something like:

SELECT * FROM tblRMA
WHERE
tblRMA.Status IN ("Shipped")
AND
tblRMA.Customer IN (Customer_B, Customer_C)
AND
...
AND
...


So the form would allow the user to choose to search all or any number of customers, or statuses, or other criteria, but I would need the code to dynamically allow for changes each time the search button is clicked. As of right now the code will go through each checkbox on the form and append a string that is passed in to the query such as:

[I havent written the code yet so some of this might have C++ influences, bear with me]

If chk_statusShipped.value = true
Then
strStatusCriteria = strStatusCriteria & " OR 'Shipped'"
Endif
If chk_statusCompleted.value = true
Then
strStatusCriteria = strStatusCriteria & " OR 'Completed'"
Endif
etc...
...
...



There's a picture of the form attached as a jpeg.


So what is the easiest way to do something like that or pass multiple parameters to a query using VBA without having a plethora of if/else statements and appending a string multiple times.

Thanks for your help

[alansidman]

This may be helpful

http://www.mrexcel.com/forum/showthread.php?t=525424

Alan

[weasel7711]

Thanks Alan. I will take a stab at it tomorrow but it seems to be what I am looking for.

[weasel7711]

Yes, that is exactly what I am looking for. It looks like I just don't know enough about Reports , as now I can specify a Where condition in the VBA code.

In case anyone has the same problem the link is here:
http://allenbrowne.com/ser-50.html

I would suggest checking out the 2002 version of the Northwind database. You can get it from Microsoft if you do a Google search. They have a 2007 version but the 2002 works with the above link.