A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old June 1st, 2010, 05:04 PM posted to microsoft.public.access.queries
Peg Hall
external usenet poster
 
Posts: 1
Default Parameter query with and multiple "NOT / OR" selection criteria

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]




  #2  
Old June 1st, 2010, 06:50 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Parameter query with and multiple "NOT / OR" selection criteria

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/For...eries/201006/1

  #3  
Old June 1st, 2010, 07:00 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Parameter query with and multiple "NOT / OR" selection criteria

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.