Exclude records if two fields are null

Posted on 2006-04-12
Medium Priority
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
  • 2
  • 2
LVL 61

Expert Comment

ID: 16437492
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

ID: 16437520

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

ID: 16437534
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

ID: 16437767
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

omgang earned 2000 total points
ID: 16438192
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

755 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