Exclude records if two fields are null

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))
****************

Thanks!
kenhaleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:

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


0
mbizupCommented:

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'

?
0
omgangIT ManagerCommented:
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
0
kenhaleAuthor Commented:
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?
0
omgangIT ManagerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.