Query using And/Or variable from form (1 Viewer)

[Steve400]

I have a search form where users select criteria, which runs the query. e.g. Will return results of location 1 and location 2.
I want to make the And a variable so that the query can search either the above or location 1 OR location 2.

I have tried replacing the And in the query with the from control but Access doesn't like it. Can this be down any other way?

Select....
From....
WHERE (((forms!ServicesCombinedSearch!Location1)=services.location Or (forms!ServicesCombinedSearch!Location1) Is Null)

iif((forms!ServicesCombinedSearch!AndOr1)="Or","OR","AND")

((forms!ServicesCombinedSearch!Location2)=services1.location Or (forms!ServicesCombinedSearch!Location2) Is Null))

 

[Pat Hartman]

You can't change the structure of a query dynamically. Queries are "compiled" and although you can pass in values as arguments, changing something like OR to AND could completely change how the query engine goes about satisfying the request.

Dynamic SQL must be built with VBA. You can then use the string to replace a recordSource or save it as a querydef.

 

[Steve400]

OK, thanks Pat,
I got around it using 2 queries, 1 for "and" and 1 for "or".
And a dropdown to select and link to desired query.