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
mriozzo68Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
0
 
PatHartmanCommented:
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.
0
 
Gustav BrockCIOCommented:
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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mriozzo68Author 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.
0
 
Gustav BrockCIOCommented:
Well, if you just "translate" on your own and don't test, it is hard to help.

/gustav
0
 
mriozzo68Author 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
0
 
Gustav BrockCIOCommented:
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
0
 
PatHartmanCommented:
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.
0
 
mriozzo68Author 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.
0
 
mriozzo68Author Commented:
Thanks Gustav for teaching me something new on a couple of different levels.
0
 
Gustav BrockCIOCommented:
You are welcome!
Have a nice weekend.

/gustav
0
 
mriozzo68Author Commented:
Well, one more thing if you can ... are wildcards able to be used in the criteria and if so, what is the syntax?
0
 
PatHartmanCommented:
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 & "')"
0
 
mriozzo68Author 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')
0
 
PatHartmanCommented:
You already accepted Gus' answer to this question.  You might want to start a new question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.