I am trying to run a query with criteria specified on a form. The form has four combo boxes from which users can choose values. (Based off the contents of the queried table.) Users will not necessarily specify answers in all four boxes. I would like the query to run the four different criteria with OR logic unless two or more boxes have answers specified. In that case, I want it to treat only the answered boxes as AND searches and ignore any unanswered boxes. If it helps, this will be used to look up sales quotations.

---

Richard Says:

This is going to be a little tricky. Your best bet would be to set up a LIST BOX on your form and then dynamically fill in the RowSource property of the list box with the data that you want using a little VBA.

For example, let's say you have two text boxes FirstName and LastName. You want the user to be able to search based on either or both of these fields. Put a command button on your form that loads the data into your list box like this:

If IsNull(FirstName) AND IsNull(LastName) Then
   MsgBox ("You must enter one!")
   Exit Sub
End If
If IsNull(FirstName) then ' just search on last name
  MyListBox.RowSource = "SELECT * FROM CustomerTable WHERE FirstName='" & FirstName & "'"
ElseIf IsNull(LastName) then ' just search on first name
  MyListBox.RowSource = "SELECT * FROM CustomerTable WHERE LastName='" & LastName & "'"
Else ' search on both names
  MyListBox.RowSource = "SELECT * FROM CustomerTable WHERE LastName='" & LastName & "' AND " & _
       FirstName='" & FirstName & "'"
End If

So as you can see, using a little VBA logic, you can get quite creative with your parameters. If you have four different boxes and you want to mix AND and OR conditions, then you can lay all of that down in your code.

I cover this in GREAT detail in my Access 311 course. It's called Dynamic SQL, and it basically involves changing the data under an object (list box, combo box, form, etc.) using your VBA code and some SQL statements.

Here's a link for more information:

http://599cd.com/site/courselist/access/access311?key=AlExBlg

Hope this helps.

Richard