Prev: Allen Browne's Has the Rcd been Printed - Taking it further
Next: Queries
From: Peg Hall Peg on 1 Jun 2010 12:04
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]




From: KenSheridan via AccessMonster.com on 1 Jun 2010 13:50
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

Peg Hall wrote:
>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]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201006/1

From: KenSheridan via AccessMonster.com on 1 Jun 2010 14:00
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

Peg Hall wrote:
>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]

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Allen Browne's Has the Rcd been Printed - Taking it further
Next: Queries