Query Issue (1 Viewer)

[kitty77]

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.

 

[Pat Hartman]

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

 

[Eugene-LS]

SELECT * FROM [Your table name]
WHERE Not (([Field1] & [Field2] & [Field3]) Is Null);

 

[theDBguy]

Just another way of writing the same thing.

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

 

[Eugene-LS]

Just another way of writing the same thing.

I agree! But there is one more logical operation
On a large number of records may be a loss of processing speed.

SELECT * FROM [Your table name]
WHERE (Len([Field1] & [Field2] & [Field3] & "")>0);

 

[theDBguy]

I agree! But there is one more logical operation
On a large number of records may be a loss of processing speed.

Just thought for beginners, it's a little bit easier to understand. Maybe? Maybe not?

 

[Eugene-LS]

Just thought for beginners, it's a little bit easier to understand. Maybe? Maybe not?

You were absolutely right!

 

[Pat Hartman]

Was this a contest? Did anyone offer any additional value or was this just, showing how many variations of syntax are possible?

 

[plog]

You new here? For the last few years this site's been becoming more about showing off answers than providing solutions. or imparting wisdom.

 

[Pat Hartman]

It seems that if the original answer solves the problem, it might be reasonable to wait to see if the OP needs more help rather than confusing the issue with unnecessary alternatives. Feel free to offer "better" options but at least have a reason. Different isn't better unless you've used isladogs big file and know for a fact that your solution will save .ooo3 seconds.

 

[The_Doc_Man]

In light of the above comments, I will merely point out to @kitty77 that you now have several suggestions on the "no blank lines" aspect of it. IF you have any questions about why or how any of them work, you surely can come back and ask.

 

[kitty77]

Thanks for all the suggestions.

 

[Mike Krailo]

I thought this was over after post #3, but I was wrong.

 

[arnelgp]

this is simpler:

Select * From yourTable Where (([Field1]+[Field2]+[Field3]) Is Null);

 

[Pat Hartman]

Now I know it is a contest
BUT, @arnelgp 's latest entry won't work. Using the + as a concatenation operator turns any concertation to null when One or both fields are null. So, a + null + null = null ; a + b + null = null; null + null + null = null;
The only non-null result is a + b + c

If you want to use a concatenation approach for this problem, you need to use the & because you do NOT want to propagate null if any option is null.

The other options seem to work but are just personal variations. No one has any empirical data that any particular variation is faster or somehow better than any other. Some variations will be understandable to a novice but others won't.

So, Kitty needs to tell us which variation she likes best.

 

[plog]

Game On.

1-5: Use Pat's general method, but in a different order

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


6-11: Use Pat's general method, but use IsDate instead of testing for null:

WHERE Not (isdate(Field1)=false And isdate(Field2)=false AND isdate(Field3)=false);
+ the 5 other field permutations


12-48: Combine the prior 2 methods:

WHERE Not (isdate(Field1)=false And isdate(Field2)=false AND Field3 Is Null);
WHERE Not (Field2 Is Null And isdate(Field3)=false AND Field1 Is Null);
+ the 34 other field/function permutations


49-54: Use Pat's logic, but flip it to Not Null and ORs:

WHERE (Field1 Is Not Null Or Field2 Is Not Null Or Field3 Is Not Null);
+ the 5 other field permutations


54-60: Let the valuse resolve to true/false implicitly:

WHERE (Field1 OR Field2 OR Field3);
+ the 5 other field permutations


61-96: Combine the prior 2 methods:

WHERE (Field1 Is Not Null Or Field2 Or Field3 Is Not Null);
WHERE (Field2 Or Field3 Or Field1 Is Not Null;
+ the other 34 permutations

97-103: Make a string out of all the fields seperated by a space and then test that string's length:

WHERE len(Field1 & "-" & Field2 & " " & Field3)>2;
+ the 5 other field permutations

104-207: Build a VBA function for each of the ways listed above and call that from the WHERE:

WHERE All3FieldsNull(Field1, Field2, Field3)=False;

207-310: Build complementary functions such that they resolves to true:

WHERE AtLeastOneFieldNotNull(Field1, Field2, Field3);

 

[arnelgp]

you change it to:

Select * From yourTable Where (([Field1] & [Field2] & [Field3]) Is Null);

 

[kitty77]

Can this be created using the query wizard? I basically don't want to show any records where field1, field2, field3 are all blank.
Attached is my data.

 

[Eugene-LS]

Can this be created using the query wizard?

Yes

 

[kitty77]

Perfect!