Inserting expression into a query

[John]

Hi

I am using a form field as criteria in a query like this
[Forms]![MyForm]![MyField]. This works fine if the field has a single value
like "value1" but if the field has an expression as a value like "value1 or
value2" then the query returns nothing. Is there a way to insert the literal
value of the field "value1 or value2" into the query? I want to do it in a
way that I can change this expression at runtime.

Thanks

Regards

 

[Larry Linson]

It is certainly possible to construct an SQL statement from predetermined
values along with information entered by a user on a Form. What that will
look like depends on the SQL and the data on the Form. In your case "Value1
OR Value2" means the user is entering two search arguments, which will have
to be parsed and the SQL properly constructed.

WHERE [SomeField] = "Value1" OR "Value2", for example, is not a valid WHERE
Clause.

A valid WHERE Clause corresponding to that would be:

WHERE [SomeField] = "Value1" OR [SomeField] = "Value2"

so some intelligence must be built into the code. (There are other ways to
search for any value in a list, but that wasn't the point of this Q&A.)

Larry Linson
Microsoft Access MVP

 

[Test]

test