Simple Query Question

[Jason92]

I am having trouble with a simple query...

I am using the QBE grid that looks like this (for example):

column1 column2 column3
Animal Color firstName

I want to enter in the parameters in the grid to show me all animals except where animal = Dog and color = White.

I tried to enter in <>"Dog" and <>"White" in the parameters for the appropriate columns, but it does not show any color dog...

What am I missing?

 

[stix4t2]

Run a test on each filter one at a time. Set the <> Dog first, see what is shown. Then set the <> White.

 

[SuicidED]

hi
SELECT Table1.animal, Table1.colour, Table1.name
FROM Table1
WHERE (((Table1.animal)<>"dog")) OR (((Table1.colour)<>"white"));

In you parameter screen put <> "dog" in the first row and <> "white" in the second row.

 

[stix4t2]

SuicidED,
Your example is the problem, the results would not show any dogs or any white. The conjunction is "And", so they should both be on the same line. But heck you can't even spell color, its not about u.

 

[Spenney]

SuicidED's answer looks accurate to me. As does the spelling of 'colour' (At least in the English language anyway).;-)

SELECT Table.animal, Table.colour, Table.name
FROM Table
WHERE (((Table.animal)<>"Dog")) OR (((Table.colour)<>"White"));

As SuicidED has already posted, this SQL would return non-white dogs and non-dogs with white colouring. For example, any grey;-) dogs and white cats would be returned.

 

[straybullet]

As stix4t2 pointed out however, the original question did specify "and" rather than "or". In Jason92's example, he would like to see all animals except white dogs. I also agree with stix4t2 as to testing for each separately - afterall, doesnt do much good to pull your hair out if there arent actually any animals which meet the criteria or if there is a discrepancy in the data...
Then again, if the data is stored as numeric links to other tables (ex: AnimalTbl and ColorTbl), you'd never find "dog" or "white" in the target - youd have to search for 1,2, etc. or run a query which would pull the necessary information together and use that as the target for the query in question.

Let them hate - so long as they fear... Lucius Accius

 

[stix4t2]

It is the 'not' part that confused me. Not dog or not white would list anything not white and anything not dogs because both test would have to fail, sorry.

But do you pronounce flour, sounding like floor?

 

[SuicidED]

stix4t2 what is you're problem...I don't crticise that you can't spell in English....

 

[ZmrAbdulla]

Color" and "Colour" both are right in English.

"Color" is American
"Colour" is British

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[SuicidED]

English

• noun the language of England, now used in many varieties throughout the world.

• adjective relating to England.

— DERIVATIVES Englishness noun.

— ORIGIN Old English, related to ANGLE.

 

[ZmrAbdulla]

I mean "English Language"

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.