Add a criteria to a query as a variable string

[gemma-the-husky]

I want to be able to enter a string in a form, and have to used as a criteria in a query to include multiple options.
So, if have a query that checks for some matching records, I want to be able to use either in or like to make it easy for the user to vary the query at run time.
I can do a single option without any trouble, by just adding a function like readentrystring() but is there an easy way to make the readentrystring pass mutliple options.
so simply
readentrystring() as a crtieria works if I set the string variable to "A"

but if I want to use in or like operators, to let a user select multiple items, is there an easy way to do that?
so the following. I want to let the user just enter "A","B","C" or maybe "*A*"and have it work as

in ("A","B","C") or maybe
like "*iron*" in some circumstances

so I can build the query with a criteria in readrentrystring() or like readrentrystring() and have it operate as in ("A","B","C") or like "*iron*"

I just tried using a criteria like [enter filter]
I then entered *iron* when prompted (and some variants), but I got a no explanation ODBC error when I did that, so I am not 100% sure what is being passed to SQL Server.

 

[Minty]

Are you using a pass-through query? That would make it obvious what is being sent.

If so the Wildcard is SQL server is % not * ?

 

[ebs17]

Solutions for Jet-SQL => YourString() (public function) takes over the content of the form text field:

WHERE Instr(1, YourString(), TableField) > 0
WHERE YourString() LIKE '*' & TableField & '*'

You can use DynamicSQL: compose the filter and then the query by VBA.

You can use a parameter table. The contents are written to an additional table in a field. The parameter table is firmly integrated in the original query:

SELECT * FROM tblData
WHERE TableField IN (SELECT FieldParameter FROM tblParameters)

 

[gemma-the-husky]

Thanks for the thoughts.
After a bit of thought, and some testing I managed to get the like option to work, by having a criteria of

like [enter filter text] and entering *iron* when prompted with no quote marks needed.
I cant think how to create an in version though. The like will be fine, I think.

 

[MajP]

You may find this interesting. It is super powerful for finding potential duplicates, and gives a lot of flexibility for searching.

So the user can type in words as potential search items. Either separate just with a space or with a comma. In this case had to use a comma since looking for strings of multiple words. Then this creates a list of the the words. So you can play around to include one or more of these words. Then you can create a Like search either "anding" or "oring" the terms. Or an in search for exact match. You then can pick any of the search types or select / deselect the terms.

 

[theDBguy]

I cant think how to create an in version though.

I don't think you can use In(). Here's an article mentioning the same solutions @ebs17 mentioned earlier, just in case it helps.

Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with p...

thedbguy.blogspot.com

 

[Josef P.]

SELECT * FROM tblData
WHERE TableField IN (SELECT FieldParameter FROM tblParameters)

Or with dynamically filled auxiliary table.

SELECT * FROM tblData
WHERE TableField In (select N from TempInValues where FillInValues([Numbers (separate with ,):]))

.. is index friendly.

 

[ebs17]

Another nice function in the WHERE part, which quickly fills a table, which is then immediately used in the query.

Regardless of this, I don't think much of a popup that surprises the user. A user should know the parameters before calling the query. Therefore, one should offer a form that allows the input and can also validate the input.
If you follow the idea of the parameter table, you could also offer a small table-like form instead of the single TextBox, where you enter the values or maybe better let them be selected by ComboBox from only valid values.

 

[CJ_London]

Not suggesting this is the best way but you can pass a comma separated string to a query using IN and the eval function

assuming these are numeric values

Where eval(myfield & “ IN (“ & [enter values] & “)”) = true

for text you would need to use the replace function to replace the commas with ‘,’ plus add further quotes at start and end

and without the ability to verify the user has entered the values correctly when using an input box, prone to risks

still better to have a form where the structure can be verified as correct before passing to the query

 

[gemma-the-husky]

Thanks for the suggestions.

This is a big database with hundreds of forms, and thousands of objects, but occasional users still have one-off queries that they will reuse from time to time, but sometimes only once. They can't design queries themselves. Rather than add a new form and release a new version of the database to deal with these one-off requests, I have a table of annotated sql queries that they select from, and all I do is add a new query to the sql query table for them to select from, for each special request, as then I don't have to build and publish a new database version. They can then show the query results on screen, or save it to spreadsheet.

Sometimes, they need to select a filter for the query - say a particular sales account, or a date range, and the queries are built to accept such parameters where relevant.

Today they had something which really needed a way for users to enter multiple products, and return all the selling prices for those products. I can easily have a query that filters one product which they can specify, but it's not so easy to let them enter several product codes. I could hardcode the products into the query with an in() statement, but tomorrow they might want different products. An "in" option would have probably been the simplest for them to use. I can get by with a "like" in this particular instance though. (As in #4 above). Alternatively they could have run the query multiple times, once for each product. I was looking for the most flexible way. There's just too much data to push everything into a spreadsheet for them to filter and sort themselves. (at least tens of thousands of rows, if not hundreds of thousands).

I could have a list box for them to select the filters they want, but one time it might be sales accounts, another time depots, another time sales agents and so on. It would be easier if I could build a query that let's them enter 1,2,3,4 as the selected depots, or 5,12,17 as selected sales agents, as long as I then have an easy way to get the query to comply with that sub-selection. Even if I give them a multi-select list box, I still have to find a way to design a query that can limit the returned rows to comply with the list box selections.

@CJ_London Sometimes the filters are numbers, but probably equally frequently they will be strings.

 

[CJ_London]

Sometimes the filters are numbers, but probably equally frequently they will be strings.

convert all to strings in that case

Function test(x As Variant, s As String)

     Debug.Print Eval("'" & x & "' IN ('" & Replace(Replace(s, ",", "','"), " ", "") & "')")

End Function

second replace is to cater if the user adds spaces e.g. "1, 2, 3"

results

?test("b","A,b,c")
-1
?test("e","A,b,c") 0
0
?test(1,"1,2,3")
-1
?test(1,"1, 2, 3")
-1
not tested but in a query I would expect it to be something like

Eval("'" & [fieldname] & "' IN ('" & Replace(Replace(" & [Enter Values] & ", ",", "','") & ", " ", "") & "')")=true

 

[ebs17]

If you want to filter arbitrarily, you have to consider the used data types and the corresponding formattings. So far, only integers have been considered. However, users do everything that is possible, and they usually do not look for errors themselves.

Josef's suggestion is very flexible and fits your description quite well. When filling and using the parameter table, you would only have to branch to a field of the appropriate data type.

For one-time use performance is less important, you could load the used function with even more functionality (input validation, type checking).