Multiple Criterai from single text box (1 Viewer)

I was looking at this thread last night and had, what I thought was, a good idea

I figured that if the various criteria where separated by, say a comma, or other readily identifiable charter, then you could use the Replace() function to insert the Or operator into the string. Then use Forms!FRM_FormName!TextBoxName in the criteria of the Query. Despite trying variations on this technique I could not get the desired result.

So as a well known TV scientist was fond of asking; "Why is it so?"

I have some theories of my own, and am interested to know if it is in fact possible to make this technique work.

The problem lies in that unfortunately you cannot have any text box send the text with operators as they will not work. In order to do this you would need to use VBA to either build the query in the VBA or use a QueryDef to modify the Where Clause.

Oh, and the reason why -

When you pass a string to the query using the text box, it has implicit quote delimiters for whatever is being passed. So, if you have an OR or an AND in there, it looks to the query as if you are passing this string:

"'Value1' OR 'Value2' OR 'Value3'"
with the literal double quotes on the outside of the string, even though you didn't pass them along. And the query really needs it to be:
"Value1" OR "Value2" OR "Value3"
with the OR/AND outside of any delimters. And so therein lies the impossible task of doing it this way as you can't send it to the query in the complete way it needs, so you need to modify the actual QueryDef SQL by either doing it on the QueryDef object or by building it in VBA and then executing it - where it is read by Access in the correct way.

I hope that helps.

Thanks Bob, that confirms my theory.

I'd figured that what was getting pulled from the text box was being treated as a single string, and the inserted operator/s where simply being treated as part of that single string.

For anyone who is interested I have posted a sample based on the above discussion here.