specify multiple criteria in a form text box and pass that criteria to a query
I've searched the web and cannot seem to find a solution. I have a form with a text box that I want to allow a user to enter multiple criteria for one field. I want to them run a query that will pick up the values in this field. Just to get something to work right now I gave users seven text boxes to allow them to specify up to seven variables. The query if then using a criteria string like this...
Like Forms!Switchboard!datafield1 or like Forms!Switchboard!datafield2 or like Forms!Switchboard!datafield3 ... etc.
It's inelegant but it works. But, it does limit the user to only seven values. There are thousands of possible values so a multi-select listbox won't be efficient.
I originally tried different "" or "'" variations of picking up the criteria but nothing worked. I was also surprised that setting the criteria to just one of the datafields and then explicitly entering a criteria string as
Like "XX" or like "YY" or like "ZZ"
did not work either.
Select * From YourTable Where "some criteria string"
You will have to create the entire SQL String in VBA. Start with a hardcoded Select * From ... and concatenate the Where clause based on what's in the box. You will have to rely on them to use wild cards and quotes for this to work so they need a certain amount of knowledge to get this right.
I would give them a limited set of common options so that the row count can be reduced and then let them use the build in filter options to reduce further or export to Excel where they can filter more finely.