Not Null Query (1 Viewer)

[kitty77]

In a query, how can I exclude all records where field1, field2 and field3 are NOT null?
All three fields must have some value in it to be excluded.

Thanks

 

[theDBguy]

Hmm, didn't you already get help on this problem earlier? What happened?

Query Issue

I have a query that returns the following records. I would like it to show Field1, Field2, Field3 (no records where they are all blank and records where a value is in either field1 and/or field2 and/or field3) I'm using the query wizard but can't seem to figure out the correct sequence.

www.access-programmers.co.uk

 

[CJ_London]

in your criteria grid put

is not null

on the same line for each field.

in sql view you should end up with something like

WHERE field1 is not null and field2 is not null and field3 is not null

 

[theDBguy]

WHERE field1 is not null and field2 is not null and field3 is not null

LOL. That looks very familiar...

Query Issue

I have a query that returns the following records. I would like it to show Field1, Field2, Field3 (no records where they are all blank and records where a value is in either field1 and/or field2 and/or field3) I'm using the query wizard but can't seem to figure out the correct sequence.

www.access-programmers.co.uk

 

[kitty77]

in your criteria grid put

is not null

on the same line for each field.

in sql view you should end up with something like

WHERE field1 is not null and field2 is not null and field3 is not null

Yes, that brings back records that have a value but I want to exclude those all from view if all three fields have a value.

 

[theDBguy]

Yes, that brings back records that have a value but I want to exclude those all from view if all three fields have a value.

So, you are saying you want the opposite result, correct?

 

[plog]

Is it time for the second half of the contest? Remember, just.because a solution has been provided and even if this thread is marked 'solved', continue to post solutions.

 

[kitty77]

So, you are saying you want the opposite result, correct?

yes

 

[theDBguy]

yes

Then try negating the expression. For example:

WHERE Not (Field1 Is Not Null AND Field2 Is Not Null AND Field3 Is Not Null)

If you can only use the Query Designer, then try adding a Calculated Column at the end of your query. For example:

Field: Field1 Is Not Null AND Field2 Is Not Null AND Field3 Is Not Null
Criteria: False

Hope that helps...

 

[Pat Hartman]

Kitty, I understand that the last thread got out of hand but did you actually try my solution? Or any of the others? I only saw one which actually didn't work. HOWEVER, if you are wrong and the "empty" fields are ZLS rather than Null, then NONE of the solutions will work. Do you know if the fields in question contain ZLS or Null? Do you know how to find out?

Where Not (Field1 Is Null AND Field2 Is Null and Field3 Is Null)

Not(some expression) --- does that clarify the expression? The conditions inside the Not() are connected with "AND" so all must be true or the expression returns false so when all the conditions are true (i.e. the field is null) then the expression inside the () is true and Not(..) says select the OPPOSITE.

Where Not (Field1 Is Null AND Field2 Is Null and Field3 Is Null) -- retuns any row where any of the fields is not null
Where (Field1 Is Null AND Field2 Is Null and Field3 Is Null) == returns any row where all of the fields are null

 

[The_Doc_Man]

Seems like De Morgan's Theorem needs to come into play. You want all three fields to be not null so at first blush...

WHERE ( ( Not (Field1 Is Null ) ) AND ( Not (Field2 Is Null ) ) AND ( Not (Field3 Is Null ) ) )

I included a few extra parentheses for emphasis though in fact, due to operator precedence, the one which isolates the NOT is superfluous.

To shorten this by De Morgan's Theorem,

WHERE Not( (Field1 Is Null ) OR ( Field2 Is Null ) OR (Field3 Is Null) )

These two are logically equivalent and shorter to type. Again, I'm being a bit explicit here, but due to operator precedence, the parentheses around the "Fieldn Is Null" is again superfluous.

 

[Pat Hartman]

Doc, please check your logic. your second expression should be the one I proposed in post #2 of the previous thread. It should use AND operators rather than OR operators.

This has taken way too much time because you all thought it was a game and confused the issue.

@kitty77 please open the database and run the queries and decide which gives you the result you want.

Query1 - which I think is the answer
SELECT Table1.id, Table1.fld1, Table1.fld2, Table1.fld3
FROM Table1
WHERE Not (Table1.fld1 Is Null AND Table1.fld2 Is Null AND Table1.fld3 Is Null);

Query 2 - the opposite answer - if that is what Kitty wants
SELECT Table1.id, Table1.fld1, Table1.fld2, Table1.fld3
FROM Table1
WHERE (Table1.fld1 Is Null AND Table1.fld2 Is Null AND Table1.fld3 Is Null);

Query3 - Doc's final answer which I'm hoping is a typo
SELECT Table1.id, Table1.fld1, Table1.fld2, Table1.fld3
FROM Table1
WHERE Not (Table1.fld1 Is Null OR Table1.fld2 Is Null OR Table1.fld3 Is Null);

 

[The_Doc_Man]

Pat, do you know and understand De Morgan's Theorem, which tells you how to work your way through distribution of AND/OR/NOT? According to that theorem, when you have a distributed NOT involving a sequence of AND conjunctions, you take the NOT out and apply it to the group, but you have to also invert the AND into OR. (Works the other way around, too.)

My 1st statement was:
WHERE ( ( Not (Field1 Is Null ) ) AND ( Not (Field2 Is Null ) ) AND ( Not (Field3 Is Null ) ) )
In English that says, roughly, "where the first field is not null and the second field is not null and the third field is not null."

My 2nd statement, created using De Morgan's Theorem on the 1st one, was
WHERE Not( (Field1 Is Null ) OR ( Field2 Is Null ) OR (Field3 Is Null) )
In English that says "where it is not true that any of field 1, field 2, or field3 is null."

Since Kitty77 wanted ALL THREE of the fields to be not null, I reduced the first statement, which says "all three are not null" to the second statement that "none of the fields are null."

Can you please enlighten me as to why you think there is a typo involved? I know I'm not the world's best typist but I thought I got that one right? No, I didn't think of it as a joke. I'm sorry if I said something that made you think otherwise. But what I did was take Kitty77's request in English and applied rules of logic manipulation to reduce it to a smaller, slightly more efficient statement. It is a shame that VBA doesn't run keyhole optimization or that 2nd statement would be even more efficient.

 

[LarryE]

In a query, how can I exclude all records where field1, field2 and field3 are NOT null?
All three fields must have some value in it to be excluded.

Thanks

1. Open the query in design mode
2. Beneath each of the fields you listed, enter Is Null in the criteria box

That will give you results where Field1 is null and Field2 is null and Field3 is null.
Isn't that what you wanted?

 

[Pat Hartman]

As I read the request, and I admit I could be wrong since it is not even close to being precise, Instead of saying what she does want, she makes two negative statements. Not Null is a negative statement and exclude is a negative statement.

I think Kitty wants only rows were at least one column is not null.

All three fields must have some value in it to be excluded.

People say "all three" when they actually mean "each of the three"; just as they say "last" when they mean "most recent". That becomes the difference in our interpretations of the request.

Does Kitty mean she wants records where ALL three fields are not null OR where at least ONE field is not null?

 

[LarryE]

As I read the request, and I admit I could be wrong since it is not even close to being precise, Instead of saying what she does want, she makes two negative statements. Not Null is a negative statement and exclude is a negative statement.

I think Kitty wants only rows were at least one column is not null.

People say "all three" when they actually mean "each of the three"; just as they say "last" when they mean "most recent". That becomes the difference in our interpretations of the request.

Does Kitty mean she wants records where ALL three fields are not null OR where at least ONE field is not null?

AAAAAAAAAAAAA lol. It should take about 5 seconds of experimenting in design mode for her to get what she wants.

 

[kitty77]

This is what I'm looking for... Seems like it's still not clear. Sorry.
In my query, I don't want to see records that have ALL three fields with a value.
If ALL three have a value in those fields, I do not want that record returned.
All other combinations I want to see those records.

 

[theDBguy]

This is what I'm looking for... Seems like it's still not clear. Sorry.
In my query, I don't want to see records that have ALL three fields with a value.
If ALL three have a value in those fields, I do not want that record returned.
All other combinations I want to see those records.

Hi. Have you tried any of the suggestions yet? Did none of them work for you? Just curious...

 

[isladogs]

Here's another suggestion in case it helps
1. Create a query to select all records where all three fields have a value. Call it e.g. qryA
2. Now use the unmatched query wizard to create a query listing all records in your table that are not in qryA. Job done!

 

[CJ_London]

@kitty77 suggest you show some example data and the result required from that example data since everyone is struggling to understand the actual requirement from your description