Parameter query with and multiple "NOT / OR" selection criteria

[Peg Hall]

I get incorrect results when I do the following query:

Field a = "X"
Field b = Not "Y or Not "Z"
Field c = Between [Date 1] and [Date 2]

 

[KenSheridan via AccessMonster.com]

Your second expression will evaluate to TRUE for any value of b. If you want
it to be TRUE where b is neither Y nor Z then use b NOT IN ("Y", "Z").
Alternatively b <> "Y" AND b <> "Z".

In your third expression the equality operator is not used. Just use c
BETWEEN [Date 1] AND [Date 2]. If [Date 1] and [Date 2] are parameters they
should be declared as DATETIME to avoid their being misinterpreted as
arithmetical expressions rather than dates. An additional possible cause of
incorrect results is that, if the values in c contain non-zero times of day,
any rows with a date on Date 2 will not be returned. This can be avoided by
using c >= [Date1] AND c < [Date 2] + 1 rather than a BETWEEN...AND operation.


Ken Sheridan
Stafford, England

I get incorrect results when I do the following query:

Field a = "X"
Field b = Not "Y or Not "Z"
Field c = Between [Date 1] and [Date 2]

 

[KenSheridan via AccessMonster.com]

One further point: a NOT IN (or IN for that matter) operation does not accept
parameters, only literal values, as its value list. So either use an AND
operation as I described ( it would an OR operation for the equivalent of IN
BTW), or you'll find a couple of ways of simulating the use of parameters at:

http://support.microsoft.com/kb/100131/en-us

Ken Sheridan
Stafford, England

I get incorrect results when I do the following query:

Field a = "X"
Field b = Not "Y or Not "Z"
Field c = Between [Date 1] and [Date 2]