Link to home
Create AccountLog in
Avatar of mriozzo68
mriozzo68Flag for United States of America

asked on

specify multiple criteria in a form text box and pass that criteria to a query

Hi guys,

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.

TIA for your help
Avatar of PatHartman
Flag of United States of America image

You can't do this directly as a parameter.  You would end up with something like this:

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.
Let them type in with no quotes:

    XY ZY XZ

Then adjust your SQL:

"Select * From YourQuery Where YourSearchField In ('" & Replace(Trim(Forms!Switchboard!datafield), " ", "','") & "')"

Open in new window

Avatar of mriozzo68


Neither one of these seemed to work.  Well, I should say I tried Gustav's solution which I translated to mean the same as Pat's.

I tested my Replace statement and it created the criteria string correctly so I'm not sure why the rest of the select statement did not work.

I guess I just need to stick with seven different text boxes.
Well, if you just "translate" on your own and don't test, it is hard to help.

I guess I should've said "interpreted" instead.  I interpreted Pat's comment as giving the same advice as yours.  Only yours had the SQL statement.  I used your SQL statement, changing "YourQuery", "YourSearchField" and "datafield" to match the actual object names.

Select * From dbo_sys1_key_codes Where dbo_sys1_key_codes.source_code In ('" & Replace(Trim(Forms!Switchboard!ssel1), " ", "','") & "')

data in the ssel1 textbox on the form = 2N 6M

Returns no data.  If I just use

SELECT * FROM dbo_sys1_key_codes WHERE dbo_sys1_key_codes.source_code in ('2N', '6M')

I get 2355 records
My code is the string to build the SQL, it is not the SQL to copy/paste.
It will for >2N 6M< produce this string:

SELECT * FROM dbo_sys1_key_codes WHERE dbo_sys1_key_codes.source_code In ('2N', '6M')

Open in new window

My advice was different from Gus'.  I said that you had to build the entire string in VBA and then run it.  Gus said you could pass a string as an argument in an In() clause.  I don't believe that will work even using Replace()  but I've been wrong before.

I did not even attempt to provide code since I have no idea what your people will be typing in.  But by building a string in VBA, this is what I meant.

strSQL = "Select * From YourTable WHERE " & Me.txtField1 & " OR " & Me.txtField2 & " OR " & Me.txtField3

This assumes t6hat whatever is in the three controls is syntactically correct AND you want to use the OR operator.  Perhaps you want to AND the conditions.  Perhaps you want the user to decide.  

I'm going to guess that you will have a lot of trouble getting this to work unless your users already know SQL in which case, they should probably be using their own FE and making custom queries for themselves.
I'm completely at a loss.  I understand how your code works but have no idea where to put it.  What I though would be fairly simple is obviously beyond my modest skill set.  I'll just stick with the multiple criteria boxes.  Thanks for your help though.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Gustav for teaching me something new on a couple of different levels.
You are welcome!
Have a nice weekend.

Well, one more thing if you can ... are wildcards able to be used in the criteria and if so, what is the syntax?
In(...) uses specific values.  It does not support wildcards.  You would need to use LIKE if you needed to select based on partial strings.

Sorry Gus, it was the single quotes that threw me.  It still looks like you are ending up with everything inside a set of outer single quotes.

In ('" & Replace(Trim(Forms!Switchboard!datafield), " ", "','") & "')"
In ('" & whatevercomesoutofthereplace & "')"
Thanks Pat.  Changing the SQL statement to use LIKE works only if one value is specified.  Once I try multiple values it doesn't work :(

This is what I changed the SQL to (I'm using a semicolon to separate values).  
keysql = "SELECT dbo_sys1_key_codes.key_code FROM dbo_sys1_key_codes Where key_code LIKE ('" & Replace(Trim(Forms!Switchboard!key_sel), ";", "' or '") & "')"

When I specify multiple criteria (I used *2XH07;*9XH16 as the value) it translates to this as the criteria and pulled no data.
Like (Like '*2XH07' Or Like '*9XH16')
You already accepted Gus' answer to this question.  You might want to start a new question.