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

mriozzo68
mriozzo68 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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

/gustav

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, if you just "translate" on your own and don't test, it is hard to help.

/gustav

Author

Commented:
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
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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


/gustav
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Gus said you could pass a string as an argument in an In() clause.

No I didn't, because you can't do that. I showed the expression (string concatenation) to build  the SQL string that will work.

I want to then run a query that will pick up the values in this field.  

Don't know how you run that query, but you could simply replace its SQL property before running it:

Dim Query As DAO.QueryDef
Dim Sql As String

Sql = "Select * From dbo_sys1_key_codes Where source_code In ('" & Replace(Trim(Forms!Switchboard!datafield), " ", "','") & "')"
Set Query = CurrentDb.QueryDefs("YourQuery")
Query.SQL = Sql

Open in new window

/gustav

Author

Commented:
Thanks Gustav for teaching me something new on a couple of different levels.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
Have a nice weekend.

/gustav

Author

Commented:
Well, one more thing if you can ... are wildcards able to be used in the criteria and if so, what is the syntax?
Distinguished Expert 2017

Commented:
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 & "')"

Author

Commented:
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')
Distinguished Expert 2017

Commented:
You already accepted Gus' answer to this question.  You might want to start a new question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial