How do you modify / negate criteria based on a form

[SWStevens]

I have a form where the user inputs zip codes (from & thru). My query
criteria is "Between [Forms]![Select Lists]![FromZip] And [Forms]![Select
Lists]![ThruZip]". But I want the user to be able to select whether the
criteria should "include" or "exclude" this zip code range using a drop down
field where I have the two values of "is" or "is not."

How can I get Access to recognize or not recognize the word "NOT" in front
of my criteria statement based on a form control?

 

[Marshall Barton]

I have a form where the user inputs zip codes (from & thru). My query
criteria is "Between [Forms]![Select Lists]![FromZip] And [Forms]![Select
Lists]![ThruZip]". But I want the user to be able to select whether the
criteria should "include" or "exclude" this zip code range using a drop down
field where I have the two values of "is" or "is not."

How can I get Access to recognize or not recognize the word "NOT" in front
of my criteria statement based on a form control?

You can't without changing the query. OTOH, you can get the
desired effect with a different kind of criteria.

I get very confused trying to do this kind of thing in the
query designer. In SQL view, the WHERE clause would be
like:

(zipfield Between Forms![Select Lists]!FromZip
And Forms![Select Lists]!ThruZip) = (Forms![Select
Lists]!IsOrIsNot = "is")