Help with sample query.
in [Queries]
|
From: KARL DEWEY on 13 Apr 2010 14:35 SELECT * FROM TableA WHERE ([Gender] Not Between 1 AND 3) AND ([Gender] <>88) AND ([Gender] <>99); -- Build a little, test a little. "MN" wrote: > Thank for reply, actual value are: 1,2,3,4,88,99 And I want to see in this > field do they have a different value other than those number? > Regards, > > "KARL DEWEY" wrote: > > > SELECT * > > FROM TableA > > WHERE [Gender] Not Between 1 AND 3; > > > > -- > > Build a little, test a little. > > > > > > "MN" wrote: > > > > > Hi - I need to run a query to find out in a tableA field gender has some > > > other value than 1,2,3. > > > But this will product all records ! It was included 1,2,3,4,5...8,9. > > > If I using "AND" then it come out just records 4,5,6,7,8,9 > > > Is it weird ? Thank you for any reply . > > > > > > SELECT * > > > FROM TableA > > > WHERE gender<>1 OR gender <> 2 or gender<>3;
From: MN on 13 Apr 2010 14:47 I do like this: Select * From tableA WHERE ((gender Not Between 1 And 4) And (gender<>77) And (gender<>99)) Or isnull(gender); Thanks for any reply. "KARL DEWEY" wrote: > SELECT * > FROM TableA > WHERE ([Gender] Not Between 1 AND 3) AND ([Gender] <>88) AND ([Gender] <>99); > > -- > Build a little, test a little. > > > "MN" wrote: > > > Thank for reply, actual value are: 1,2,3,4,88,99 And I want to see in this > > field do they have a different value other than those number? > > Regards, > > > > "KARL DEWEY" wrote: > > > > > SELECT * > > > FROM TableA > > > WHERE [Gender] Not Between 1 AND 3; > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "MN" wrote: > > > > > > > Hi - I need to run a query to find out in a tableA field gender has some > > > > other value than 1,2,3. > > > > But this will product all records ! It was included 1,2,3,4,5...8,9. > > > > If I using "AND" then it come out just records 4,5,6,7,8,9 > > > > Is it weird ? Thank you for any reply . > > > > > > > > SELECT * > > > > FROM TableA > > > > WHERE gender<>1 OR gender <> 2 or gender<>3;
From: John W. Vinson on 13 Apr 2010 16:37 On Tue, 13 Apr 2010 11:14:11 -0700, MN <MN(a)discussions.microsoft.com> wrote: >Thank for reply, actual value are: 1,2,3,4,88,99 And I want to see in this >field do they have a different value other than those number? Gender NOT IN(1,2,3,4,88,99) is a compact way to find records where there is a value in Gender (i.e. not NULL) but exclude those six specific values. -- John W. Vinson [MVP]
From: Marshall Barton on 13 Apr 2010 23:47
With more than just 2 or 3 items, I agree with the others that usin IN is cleaner: WHERE Not gender In(1, 2, 3, 77, 99) Or gender Is Null -- Marsh MVP [MS Access] MN wrote: >I mean I like the AND than but the logical here somthing make me confused? >OK-The user want to print out all of record if the field gender have >diferent value other than 1,2,3,4,77,99 and you was right null is count too. > >"Marshall Barton" wrote: > >> MN wrote: >> >> >Hi - I need to run a query to find out in a tableA field gender has some >> >other value than 1,2,3. >> >But this will product all records ! It was included 1,2,3,4,5...8,9. >> >If I using "AND" then it come out just records 4,5,6,7,8,9 >> >Is it weird ? Thank you for any reply . >> > >> >SELECT * >> >FROM TableA >> >WHERE gender<>1 OR gender <> 2 or gender<>3; >> >> >> And what about the AND didn't you like? Seems like the 4,5 >> .... are the ones you want to see?? >> >> If the problem is that you also have some records where the >> gender field is Null, then: >> >> WHERE (gender<>1 AND gender <> 2 AND gender<>3) >> OR gender Is Null |