Is Not Null...Multiple fields

[msk7777]

I may not be searching correctly and that is why I can't find my answer. So I apologize if this is in the database.



I am creating a query to scrub out certain records from a table. How would I exclude records where BOTH the "Phone" and "Email" fields are null? I do not want to exclude them if "Phone" field is not null but the "Email" field is, or vice versa.

I currently have tried WHERE ((([RAWCancels]![Phone number] And [RAWCancels]![Email]) Is Not Null)), but it is not working.

I'm sure I am just overlooking something here, but I have hit a wall trying to figure out how to write the criteria. Thanks in advance for any assistance.

[hertfordkc]

I may not be searching correctly and that is why I can't find my answer. So I apologize if this is in the database.

I am creating a query to scrub out certain records from a table. How would I exclude records where BOTH the "Phone" and "Email" fields are null? I do not want to exclude them if "Phone" field is not null but the "Email" field is, or vice versa.

I currently have tried WHERE ((([RAWCancels]![Phone number] And [RAWCancels]![Email]) Is Not Null)), but it is not working.

I'm sure I am just overlooking something here, but I have hit a wall trying to figure out how to write the criteria. Thanks in advance for any assistance.

----------------------------------------------------------------------------------------------------------------------------

WHERE ( ([RAWCancels]![Phone number] Is Not Null) And ([RAWCancels]![Email] Is Not Null))

[msk7777]

Thank you for responding, however, that didn't work either. I still show records where the [Phone number] field and [Email] field are both blank.

Here is the entire SQL code

INSERT INTO DataScrubPart1_RemoveActiveCancels ( [Member ID], [First Name], [Last Name], Addr1, Addr2, City, State, Zip, [Phone number], Email, [Group Code], [Group Name], [Effective Date], [Cancellation Date], [Allow Careington Email Solicitation], [Allow 3rd Party Email Solicitation], [Allow Postal Mail Solicitation], [Cancel Reason], [Cancel Note], [Group Agent ID/Sales Summary #], [Phone number], Email )
SELECT RAWCancels.[Member ID], RAWCancels.[First Name], RAWCancels.[Last Name], RAWCancels.Addr1, RAWCancels.Addr2, RAWCancels.City, RAWCancels.State, RAWCancels.Zip, RAWCancels.[Phone number], RAWCancels.Email, RAWCancels.[Group Code], RAWCancels.[Group Name], RAWCancels.[Effective Date], RAWCancels.[Cancellation Date], RAWCancels.[Allow Careington Email Solicitation], RAWCancels.[Allow 3rd Party Email Solicitation], RAWCancels.[Allow Postal Mail Solicitation], RAWCancels.[Cancel Reason], RAWCancels.[Cancel Note], RAWCancels.[Group Agent ID/Sales Summary #], RAWCancels.[Phone number], RAWCancels.Email
FROM RAWCancels
WHERE (((RAWCancels.[Phone number]) Is Not Null) AND ((RAWCancels.Email) Is Not Null))
GROUP BY RAWCancels.[Member ID], RAWCancels.[First Name], RAWCancels.[Last Name], RAWCancels.Addr1, RAWCancels.Addr2, RAWCancels.City, RAWCancels.State, RAWCancels.Zip, RAWCancels.[Phone number], RAWCancels.Email, RAWCancels.[Group Code], RAWCancels.[Group Name], RAWCancels.[Effective Date], RAWCancels.[Cancellation Date], RAWCancels.[Allow Careington Email Solicitation], RAWCancels.[Allow 3rd Party Email Solicitation], RAWCancels.[Allow Postal Mail Solicitation], RAWCancels.[Cancel Reason], RAWCancels.[Cancel Note], RAWCancels.[Group Agent ID/Sales Summary #]
HAVING (((RAWCancels.[Cancel Note])="Automated System cancellation for non-payment" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO INSUFFICIENT FUNDS" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO INVALID ACCOUNT" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO NO ACCOUNT/UNABLE TO LOCATE ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: ACCOUNT CLOSED" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NO ACCT/ UNABLE TO LOCATE ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NON TRANSACTION ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NSF" Or (RAWCancels.[Cancel Note])="INSUFFICIENT FUNDS" Or (RAWCancels.[Cancel Note])="NO ACCOUNT/UNABLE TO LOCATE ACCOUNT"));

[hertfordkc]

WHERE NOT ( ([RAWCancels]![Phone number] Is Null) And ([RAWCancels]![Email] Is Null))

[msk7777]

Thanks for joining in on my headache! Unfortunately it didn't work either. I still have records where both fields are null.

[msk7777]

Ok I changed it to

WHERE ((Not (RAWCancels.[Phone number])="") AND (Not (RAWCancels.Email)=""))

and it seems to be a step forward, however, written this way it takes out all records where either field is "". I just need a tweek to make it only exclude records where BOTH fields are blank. Any ideas???

I have also tried

WHERE ((([RAWCancels]![Phone number] And [RAWCancels]![Email])<>""))

It didn't work either.

[hertfordkc]

WHERE NOT ( ([RAWCancels]![Phone number] Is Null) And ([RAWCancels]![Email] Is Null))

---------------------------------------------------------------------------------------------------------------------------------

on your last post, I think it will do the job.

[msk7777]

Ok maybe we are off. However, I have tried all formats in all messages above and none have worked. I have copied your

WHERE NOT ( ([RAWCancels]![Phone number] Is Null) And ([RAWCancels]![Email] Is Null)) exactly as-is and it is not working unfortunately.

Here is the full code if that helps...

INSERT INTO DataScrubPart1_RemoveActiveCancels ( [Member ID], [First Name], [Last Name], Addr1, Addr2, City, State, Zip, [Phone number], Email, [Group Code], [Group Name], [Effective Date], [Cancellation Date], [Allow Careington Email Solicitation], [Allow 3rd Party Email Solicitation], [Allow Postal Mail Solicitation], [Cancel Reason], [Cancel Note], [Group Agent ID/Sales Summary #] )
SELECT RAWCancels.[Member ID], RAWCancels.[First Name], RAWCancels.[Last Name], RAWCancels.Addr1, RAWCancels.Addr2, RAWCancels.City, RAWCancels.State, RAWCancels.Zip, RAWCancels.[Phone number], RAWCancels.Email, RAWCancels.[Group Code], RAWCancels.[Group Name], RAWCancels.[Effective Date], RAWCancels.[Cancellation Date], RAWCancels.[Allow Careington Email Solicitation], RAWCancels.[Allow 3rd Party Email Solicitation], RAWCancels.[Allow Postal Mail Solicitation], RAWCancels.[Cancel Reason], RAWCancels.[Cancel Note], RAWCancels.[Group Agent ID/Sales Summary #]
FROM RAWCancels
WHERE NOT ( ([RAWCancels]![Phone number] Is Null) And ([RAWCancels]![Email] Is Null))
GROUP BY RAWCancels.[Member ID], RAWCancels.[First Name], RAWCancels.[Last Name], RAWCancels.Addr1, RAWCancels.Addr2, RAWCancels.City, RAWCancels.State, RAWCancels.Zip, RAWCancels.[Phone number], RAWCancels.Email, RAWCancels.[Group Code], RAWCancels.[Group Name], RAWCancels.[Effective Date], RAWCancels.[Cancellation Date], RAWCancels.[Allow Careington Email Solicitation], RAWCancels.[Allow 3rd Party Email Solicitation], RAWCancels.[Allow Postal Mail Solicitation], RAWCancels.[Cancel Reason], RAWCancels.[Cancel Note], RAWCancels.[Group Agent ID/Sales Summary #]
HAVING (((RAWCancels.[Cancel Note])="Automated System cancellation for non-payment" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO INSUFFICIENT FUNDS" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO INVALID ACCOUNT" Or (RAWCancels.[Cancel Note])="CANCELED DUE TO NO ACCOUNT/UNABLE TO LOCATE ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: ACCOUNT CLOSED" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NO ACCT/ UNABLE TO LOCATE ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NON TRANSACTION ACCOUNT" Or (RAWCancels.[Cancel Note])="DRAFT REJECTED: NSF" Or (RAWCancels.[Cancel Note])="INSUFFICIENT FUNDS" Or (RAWCancels.[Cancel Note])="NO ACCOUNT/UNABLE TO LOCATE ACCOUNT"));


when the query is ran it has several records with nothing in both fields.

[hertfordkc]

[QUOTE=msk7777;89223]Ok maybe we are off. However, I have tried all formats in all messages above and none have worked. I have copied your

WHERE NOT ( ([RAWCancels]![Phone number] Is Null) And ([RAWCancels]![Email] Is Null)) exactly as-is and it is not working unfortunately. [/QUaOTE]

what SQL demands in the way syntax for field references. Try substituting the dot references, i.e. RAWCancels.Phone number Is Null) And (RAWCancels.Email

SQL gives me headaches.

[msk7777]

Thanks for continuing to try to help me, it is greatly appreciated. My head is pounding because I keep slamming it into the keyboard so often from trying to figure this out.

Ok I changed it to WHERE NOT ( (RAWCancels.[Phone number] Is Null) And (RAWCancels.[Email] Is Null)) and got the same results. Uugh, there has to be a way to make this work! It's a simple request in Excel, however not a simple request in Access lol

I think I was headed in the right direction earlier by asking for <>"". Maybe going back down that path of not asking for Null but asking for <>""???

[hertfordkc]

I'm VBA oriented and the brackets may be messing up the SQL. From your comments, it sounds as if the rest of the query is delivering the records you expect to see, but something in the WHERE clause is not functioning correctly, i.e. not excluding a few where both fields are null. Which leaves me believing the problem lies with the field references.

Testing <>"" is testing for empty as opposed to Null. If you consider Null and Empty to be equivalent, then perhaps you should make nulls show as empty and test for empty. I usually construct queries in the design view and almost routinely create a calculated field with NZ(Fieldname,""). That allows me to just test for an empty string.

[msk7777]

if I remove all brackets then I get a syntax error message. I also do all my queries through the design view as well because the SQL makes me go crazy.

All I've done here is import the data from an excel workbook. Now trying to create queries to scrub unnecessary records from the data. So according to what you were saying I think the records have "empty" fields instead of null because all of the different ways we have tried as Null has had little to no results. But when I tried <>"" it removed a lot of records...unfortunately it removed too many records.

Now after reading the last part of your message, you are obviously way further along with Access than I am because you lost me. I guess the question I need to ask is how would you design the query if the fields were empty instead of null?

[msk7777]

Eureka!!!!! I got it, I adjusted your code to this and it worked 100%:

WHERE NOT ( (RAWCancels.[Phone number] ="") And (RAWCancels.[Email] =""))

Thanks for helping me figure this out!!!!

[hertfordkc]

As I said earlier, you might put in calculated fields to cover this possibility.
Sorry it took so long.