Query from a text box 1

[GlenLynam]

I have a query that i am trying to run that takes its constant from a text box on a form. Whic h is ok

forms!frmx!txtbox

BUT, in the text box i have more than one thing to search on ie if the contents of the text box was to read...

'item1 , item2 , item3 , '

I want the query to lookup on all of these so it would find all the item 1's 2's and 3's. Thanks in advance for anyhelp offered.

Glen

 

[HarleyQuinn]

You could use the Replace function to replace the comma's with OR's, something like:

Dim strUpdatedSearch as String

strUpdatedSearch = Replace(txtbox.Value, " , ", Chr(39) & " or " & Chr(39))

That will replace all instances of space comma space with ' OR '
You can then use the variable as the criteria rather than the textbox. You may have to play around with the leading and trailing single quotes but the basic idea is there for you to work on.

Hope this helps

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[PHV]

You may try this (provided NO single quote in the TextBox):
WHERE InStr(', ' & [Forms]![frmx]![txtbox], ', ' & [item field] & ' ,') > 0


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GlenLynam]

Thank you for your replies but im afraid neither appear to work.

The first doesnt like he or's, i ran the query and typed it in manually in the box that asked for the contents of [forms]![frmx]![txttemp] i tried numerous combinations with and without speech marks and no joy, it seems to take the contents of that box as a string of text and doesnt care about the OR. Is here a way to change this behaviour?

And the second keeps telling me invalid syntax and highlighting the WHERE.

Any more ideas / suggestions will be greatly appreciated.

Regards

Glen

 

[PHV]

For mine suggestion you have to go in SQL view.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GlenLynam]

PHV your a legend, had to tweak the input txtfield format slightly added a comma at the end as it was initially missing the last one of but all sorted now.

Thanks to both of you for taking the time to respond!