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!datafiel
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
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!datafiel
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
Microsoft Access
Last Comment
PatHartman
Let them type in with no quotes:
XY ZY XZ
Then adjust your SQL:
XY ZY XZ
Then adjust your SQL:
"Select * From YourQuery Where YourSearchField In ('" & Replace(Trim(Forms!Switchboard!datafield), " ", "','") & "')"
ASKER
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.
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.
/gustav
/gustav
ASKER
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_
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_
I get 2355 records
Select * From dbo_sys1_key_codes Where dbo_sys1_key_codes.source_
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_
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:
/gustav
It will for >2N 6M< produce this string:
SELECT * FROM dbo_sys1_key_codes WHERE dbo_sys1_key_codes.source_code In ('2N', '6M')
/gustav
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 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.
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Gustav for teaching me something new on a couple of different levels.
You are welcome!
Have a nice weekend.
/gustav
Have a nice weekend.
/gustav
ASKER
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!Switchb
In ('" & whatevercomesoutoftherepla
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!Switchb
In ('" & whatevercomesoutoftherepla
ASKER
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_cod
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')
This is what I changed the SQL to (I'm using a semicolon to separate values).
keysql = "SELECT dbo_sys1_key_codes.key_cod
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.
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.