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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|