Are you using 2 listboxes, 1 for each state selected? Does your criteria for state in your query then have something like (like Forms!MySearchForm!Statelistbox1 & "*" or like Forms!MySearchForm!Statelistbox2 & "*")? If you have 1 multi-select State listbox then Nick is right, you need to walk through the values in the listbox and construct a vba statement. Look at his example to do this.
And then do you also have some date criteria in the same query matching against the date search value on the form?
If you want to use that same query and return values if there is a blank name in the table (without writing vba), you can do an expression in the query for the name column (ie SName: iif(isnull(Forms!MySearchForm!MySearchNameField)," X",[NameField]) and then in the criteria for that column in the query, put... like Forms!MySearchForm!MySearchNameField or like "X"). It doesn't have to be "X". It could also be some other character.
What you're doing is fooling the query by saying in the expression....If the Name search value on the form is null, show an "X" in this column (to show all values), otherwise return the value of [NameField]. And then in the criteria for that column you're saying... like the search field value for Name on the form or like "X" if no search value for Name is selected.
You can also do the same method for the Date column in the query as you do for the Name column in the query, using an old date value in the expression which would never be entered.
Here's an example of what I mean on how to do it in the query without building a vba statement (SearchForm and Query2). The only vba code in the SearchForm is to open the query but if you don't select a State in either one on the listboxes on the SeachForm, you won't get any returns (but you can make these like the Name field). But you can have blank Name values or blank date values in some of the records which is usually the problem, or leave these blank on the SearchForm and also return records. Look at the difference between Query2 and Query2x.
I hope that is what you were getting at.
Last edited by pkstormy; 08-04-07 at 03:10.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)