Multiple Criteria in a Query does not seem to work

[disneygoof via AccessMonster.com]

Greetings all...

Thank you all for all the help you have provided over the course of my
database building...

Now, I have a nother issue...

I am tryng to query a table of data using multiple criteria and it does not
seem to work the way I think it should...suprise, suprise!!!

I have multiple fields in the query (A, B, C, D, E, F and G) for example

I need to return all "C" if these conditions are true...
A is null (Date/TIme)
B = 0 (Yes/No)
C > D or C is null (Date/TIme)
D (Date/Time) <--no criteria here
E is null (Date/Time)
F is null (Date/Time)
G = 0 (Yes/No)

There are other fields I return all data...but they shouldn't be an issue...
No criteria on them

What does not seem to work is the C>D or C is null Criteria. I have tried
AND, I have added them into the parameter section, I have tried TWO seperate
lines under criteria...I am at a loss???

I believe the NULL items ar coming accross correctly (many to verify), but
when not C>D...I am getting C>D and D>C data returning. If I use AND, I do
not get the C is Null, but I get the C>D.

Thoughts???
Thanks,
David

 

[Marshall Barton]

disneygoof via AccessMonster.com wrote:.

I am tryng to query a table of data using multiple criteria and it does not
seem to work the way I think it should...suprise, suprise!!!

I have multiple fields in the query (A, B, C, D, E, F and G) for example

I need to return all "C" if these conditions are true...
A is null (Date/TIme)
B = 0 (Yes/No)
C > D or C is null (Date/TIme)
D (Date/Time) <--no criteria here
E is null (Date/Time)
F is null (Date/Time)
G = 0 (Yes/No)

There are other fields I return all data...but they shouldn't be an issue...
No criteria on them

What does not seem to work is the C>D or C is null Criteria. I have tried
AND, I have added them into the parameter section, I have tried TWO seperate
lines under criteria...I am at a loss???

I believe the NULL items ar coming accross correctly (many to verify), but
when not C>D...I am getting C>D and D>C data returning. If I use AND, I do
not get the C is Null, but I get the C>D.

Each expression in a criteria row is combined with ANDs,
Then each row of ANDs are combined using ORs.

I know that sounds confusing, but it means that putting the
Null on a separate row that does not have any other criteria
means that the query will return any records that have Null
in that field. Logically, that would be like:
(A Is Null AND C>D AND E Is Null) OR (C Is Null)
Clearly that is not what you want.

OTOH, putting all the criteria in one row and using AND in
the C column will result in:
A Is Null AND (C>D AND C Is Null) AND E Is Null
results in an empty set because C can never be >D and Null
at the same time.

If you set the C criteria to:

D OR Null

then you will get what I think you want:
(A Is Null AND (C>D OR C Is Null) AND E Is Null

 

[disneygoof via AccessMonster.com]

Cool!!! thanks...
David

disneygoof via AccessMonster.com wrote:.

I am tryng to query a table of data using multiple criteria and it does not
seem to work the way I think it should...suprise, suprise!!!

[quoted text clipped - 20 lines]

when not C>D...I am getting C>D and D>C data returning. If I use AND, I do
not get the C is Null, but I get the C>D.

Each expression in a criteria row is combined with ANDs,
Then each row of ANDs are combined using ORs.

I know that sounds confusing, but it means that putting the
Null on a separate row that does not have any other criteria
means that the query will return any records that have Null
in that field. Logically, that would be like:
(A Is Null AND C>D AND E Is Null) OR (C Is Null)
Clearly that is not what you want.

OTOH, putting all the criteria in one row and using AND in
the C column will result in:
A Is Null AND (C>D AND C Is Null) AND E Is Null
results in an empty set because C can never be >D and Null
at the same time.

If you set the C criteria to:
>D OR Null
then you will get what I think you want:
(A Is Null AND (C>D OR C Is Null) AND E Is Null