Exclude records if two fields are null

Posted on 2006-04-12
Last Modified: 2008-03-17
I am using Access as a front end for SQL Server in this case.  Below is the current query that I need to add the following criteria:
I need to exclude records from the result set if both the dbo.Membership_20060411.Phone AS ULTRAPhone and dbo.qryUnionNetMemberInfo.DispatchPhone fields are null.  If either phone number exists I need the record to appear.

SELECT     dbo.Membership_20060411.Member_ID, dbo.Membership_20060411.First_Name, dbo.Membership_20060411.Last_Name,
                      dbo.Membership_20060411.Zip_Postal, dbo.Membership_20060411.State_Prov, dbo.Membership_20060411.Class,
                      dbo.qryUnionNetMemberInfo.RegisteredVoter, dbo.qryUnionNetMemberInfo.RegisteredVoterDate, dbo.Membership_20060411.Phone AS ULTRAPhone,
                      dbo.qryUnionNetMemberInfo.DispatchPhone, dbo.Membership_20060411.County, dbo.tblCounty.Cluster
FROM         dbo.Membership_20060411 INNER JOIN
                      dbo.tblCounty ON dbo.Membership_20060411.County = dbo.tblCounty.County LEFT OUTER JOIN
                      dbo.qryUnionNetMemberInfo ON dbo.Membership_20060411.Member_ID = dbo.qryUnionNetMemberInfo.UBCNumber LEFT OUTER JOIN
                      dbo.qrySOURCE_RegisteredVoters_AllPhones ON
                      dbo.Membership_20060411.Member_ID = dbo.qrySOURCE_RegisteredVoters_AllPhones.MEMBER_ID
WHERE     (dbo.qrySOURCE_RegisteredVoters_AllPhones.MEMBER_ID IS NULL) AND (dbo.Membership_20060411.State_Prov = N'CA') AND
                      (NOT (dbo.tblCounty.Cluster IS NULL))

Question by:kenhale
    LVL 61

    Expert Comment

    Try this:

    Where (nz(dbo.qrySOURCE_RegisteredVoters_AllPhones.MEMBER_ID,"") = "") AND (dbo.Membership_20060411.State_Prov = N'CA') AND
                          (NOT (nz(dbo.tblCounty.Cluster ,"")= ""))

    LVL 61

    Expert Comment


    Where (nz(dbo.qrySOURCE_RegisteredVoters_AllPhones.MEMBER_ID,"") = "") AND(dbo.Membership_20060411.State_Prov = N'CA') AND  (NOT (nz(dbo.tblCounty.Cluster ,"")= ""))

    I think that this will cause trouble, too: dbo.Membership_20060411.State_Prov = N'CA'

    WHat are you testing for here?  SHould it be:

    dbo.Membership_20060411.State_Prov = 'NCA'

    LVL 28

    Expert Comment

    Create a calc'd field something like
    Nz([dboMembership_2006411.Phone],"")&Nz([dbo.qryUnionNetMemberInfo.DispatchPhone],"") AS NullPhone

    and set the criteria for this field to <> ""

    OM Gang

    Author Comment

    The current WHERE statement is testing for the following:
    1. Does the Member ID in dbo.Membership_20060411 exist in dbo.qrySOURCE_RegisteredVoters_AllPhones?  If so, don't include it in the results.
    2. Is the State CA?  If not, exclude it.
    3. Do they live in Northern California?  If not, exclude it.

    So I am attempting to add one last check to see if a phone number exists in either of the phone number fields.

    I would like to try the calculated field but am unsure how to execute it.  Would that statement be in the SELECT area?
    LVL 28

    Accepted Solution

    Yes, add the statement to the Select area and then add to the WHERE clause

    WHERE     (dbo.qrySOURCE_RegisteredVoters_AllPhones.MEMBER_ID IS NULL) AND (dbo.Membership_20060411.State_Prov = N'CA') AND
                          (NOT (dbo.tblCounty.Cluster IS NULL) AND (Nz([dboMembership_2006411.Phone],"")&Nz([dbo.qryUnionNetMemberInfo.DispatchPhone],""))<>""

    OM Gang

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    730 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

    17 Experts available now in Live!

    Get 1:1 Help Now