Query By Form with OR in text box?

[Guest]

Can someone explain to me if it is possible to have OR criteria in a text box
using QBF? If I set up a simple table and a query that has the criteria of
[forms]![MyForm].[txtMyTextBox] and create a text box called txtMyTextBox on
a form named MyForm, put in a value in the text box and run the query it
filters correctly. If I put in another value in the text box and rerun the
query, it works correctly again. However, I need to be able to enter
something like "a OR b" into the text box, but it always returns no records.
I've tried:
"a" OR "b"
'a' OR 'b'
a OR b

Nothing works. Can I do something like this?

 

[Douglas J Steele]

You can't do it.

Under the covers, Access is putting something like

WHERE MyFIeld = [forms]![MyForm].[txtMyTextBox]

as the SQL of the query. When you provide a single value, that translates to

WHERE MyFIeld = "a"

which is valid. However, when you provide something like "a" or "b", that
translates to

WHERE MyField = "a" or "b"

which is not valid SQL: valid would be WHERE MyField = "a" or MyField = "b"

One thing you can do is go into the SQL associated with your query (you can
do this by selecting SQL View from the View menu when the query's open) and
changing it from

WHERE MyFIeld = [forms]![MyForm].[txtMyTextBox]

to

WHERE InStr("," & [forms]![MyForm].[txtMyTextBox] & ",", "," & MyField &
",") > 0

Then, enter a,b in your text box to get all rows with either a or b in
MyField.

In case you're wondering, the reason for concatenating "," before and after
the text box, and before and after the field name is to ensure that only
complete strings are matched.

 

[Guest]

Thanks Doug!

I was sure that it was possible since I could go into the query designer and
in the Criteria row set "a" OR "b". Thanks!!

 

[Douglas J Steele]

Type "a" or "b" into the criteria row, and then open the SQL View of the
query to see what Access does under the covers.