Query Issue

[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.

 

[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!

 

[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.

 

[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);

 

[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!

 

[isladogs]

OK ...so who is going to do a speed comparison test on all of these variations and see whether there really is any noticeable difference between them?
In this case, not me!

 

[The_Doc_Man]

In this case, a speed test on arnelgp's #17 would be on what is PROBABLY the fastest choice, but with the understanding that the general case might be content-sensitive i.e. speed will depend on the number of nulls involved. The ampersand will try to concatenate something but it has to test first whether there IS anything to concatenate. Therefore there is a two-step process, the second step of which will be skipped when there IS a null in a field. (Actually, three two-step processes, one for each field.) Therefore the speed test would be expected to show a wider range of timing.

 

[GPGeorge]

I can verify that functions in SQL can be a tremendous performance hit, but it probably does vary with context, so an absolute statement is hard to pin down. The particular place where this came home to me was when we migrated the back end to SQL Server and queries that called Access functions ground to a halt. We moved most of those functions to stored procedures or functions in SQL Server, and others we revised the SQL in the Access queries. I recall that situation with clarity because most of the project budget that should have gone to enhancements went into remediating that performance problem.