Query to exclude records with null values across multiple fields

Posted on 2011-04-28
Last Modified: 2012-06-21
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.
Question by:GIStewart
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    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)

    Author Comment

    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.
    LVL 3

    Accepted Solution

    try this :

    SELECT *
    FROM Table1 where FieldA <> NULL or FieldB <> NULL or FieldC <> NULL or FieldD <> NULL;

    Author Comment

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now