• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Query to exclude records with null values across multiple fields

I am sure this is easy for everyone but me!

I have a table with 4 fields, and each record can have between zero and 4 values across these fields.  I am looking for a query that includes any record with at least 1 value across these fields, and excludes any with none.  For example, in the attached sample file, records 4, 7 and 9 would not be displayed in the query, but the rest would be.
EE-sample-Apr29.mdb
0
GIStewart
Asked:
GIStewart
  • 2
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
Select (iff(isnull(column1),1,0)) as col1,(repeat) where col1+col2+col3+col4 >= 1

Or select * where (col1 is not null or col2 is not null or col3 is not null or col 4 is not null)
0
 
GIStewartAuthor Commented:
I renamed my columns col1, col2, col3, col4, and then used the following code in a query:

SELECT * from Table1 where (Col1 is not null or Col2 is not null or Col3 is not null or Col 4 is not null);

but i get a syntax error (missing operator) message.
0
 
ssisworoCommented:
try this :

SELECT *
FROM Table1 where FieldA <> NULL or FieldB <> NULL or FieldC <> NULL or FieldD <> NULL;
0
 
GIStewartAuthor Commented:
Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now