one unbound textfield with seperating commas into multiple criteria

[jammerdk]

Hi guys

I would like to turn in my unbound form field when seperated with commas into a multiple search criteria.

I've almost done this succesfully with the following code

SELECT *
FROM tbl1
WHERE (((InStr("," & Forms!frmTest!txtFruit & ",","," & [Fruit] & ","))>0));

However using this code the user have to type in the exact string.



ex.

Textfield : Apple,Banana

Returns.....

Apple
Banana

Instead would I like the user being able to throw in a wildcard symbol

ex.

Textfield : *pl,Ba*

Returns.....

Apple
Banana

Any ideas to solve this problem?

[Xipooo]

Drop your textbox data into an Array using the split() function. Then you can do a foreach loop through your array, then in your query use the Like expression. Insert your results into a results table and then use that results table to make your form.

Dim strSearch() as string
Dim item as variant

strSearch = split(Forms!frmTest!txtFruit)
currentdb.execute "DELETE * FROM tblResults"

Foreach item In strSearch 
  currentdb.execute "INSERT INTO tblResults SELECT * FROM tbl1 WHERE Fruit LIKE '" & item & "'" 
Next

[jammerdk]

Might mention my tables where my quieries gather information from only are linked.

I'd tried your proposal though and with the minor tweaks (split clause and currentdb.execute) and it did work, but I would like is a solution within the query itself. (rather not add tables)

[Xipooo]

Yeah, here's a faster way to skin the cat and do what you want. Dunno why I didn't think if it first actually.

dim strSQL as string
dim strFruits() a string
dim x as integer
strFruits = split(Forms!frmTest!txtFruit)
strSQL = "SELECT * FROM tbl1"

for x = 0 to ubound(strFruits)
   if x = 0 then 
    strSQL = strSQL & " WHERE Fruit Like '" & strFruits(x) & "'"
   else
    strSQL = strSQL & " OR Fruit Like '" & strFruits(x) & "'"
   end if
next x
currentdb.querydefs("<queryname>").SQL = strSQL

[orange]

Here's a small database based on Xipoo's response.
Created with 2010 saved as mdb

Open the form, enter your search terms in text box and clickToSearch
If you don't use a search term, you get all records.

Good luck

[Xipooo]

Here's a small database based on Xipoo's response.
Created with 2010 saved as mdb

Open the form, enter your search terms in text box and clickToSearch
If you don't use a search term, you get all records.

Good luck

Thanks for putting that together Orange.