Query for Label where field is null

[cshipley83]

Hi. I'm trying to create a query for labels. I want it to filter by Support Field contains X and Email Field is empty. I can't seem to type in the right thing for the email empty criteria. Please help. Thanks.

[JoeM]

On the Criteria line for that field, just use:

Is Null

[cshipley83]

I am using Is Null in the email fields criteria, but when I run the query it's not bringing anything up.

I need Support Field to contain X, so in the Criteria I have "X"
AND
I need the Email Field to be empty, so in the Criteria I have "IsNull"

When query is run, nothing is showing

[JoeM]

It is two words "Is Null", not "IsNull".

If you are still having issues getting it to work, please switch your query to SQL View and copy and paste the SQL code here for us to analyze.

[cshipley83]

SELECT DONORS.ACCTID, DONORS.TITLE, DONORS.FNAME, DONORS.LNAME, DONORS.ADDR1, DONORS.CITY, DONORS.STATE, DONORS.ZIP, DONORS.COUNTRY, DONORS.SUPPORT1, DONORS.EMAIL1, DONORS.EMAIL2
FROM DONORS
WHERE (((DONORS.SUPPORT1)="X") AND ((DONORS.EMAIL1)="Is Null") AND ((DONORS.EMAIL2)="Is Null"))
ORDER BY DONORS.ACCTID;

[JoeM]

When you place "Is Null" between quotes in your criteria, it is looking for a literal text entry of "Is Null". If you want to check if the entries are actually Null, don't use the double quotes, i.e.

WHERE (((DONORS.SUPPORT1)="X") AND ((DONORS.EMAIL1) Is Null) AND ((DONORS.EMAIL2) Is Null))

Note how if post #2 when I showed you what to enter in on the Criteria line, I did not use any double-quotes.

[cshipley83]

Took out the "", still does not show the data.


SELECT DONORS.ACCTID, DONORS.TITLE, DONORS.FNAME, DONORS.LNAME, DONORS.ADDR1, DONORS.CITY, DONORS.STATE, DONORS.ZIP, DONORS.COUNTRY, DONORS.SUPPORT1, DONORS.EMAIL1, DONORS.EMAIL2
FROM DONORS
WHERE (((DONORS.SUPPORT1)="X") AND ((DONORS.EMAIL1) Is Null) AND ((DONORS.EMAIL2) Is Null))
ORDER BY DONORS.ACCTID;

[JoeM]

I set up an example like yours, and the code worked for me.

So that makes me question whether these EMAIL fields are really null/empty, or they contain something like a single space in them.

Try running this code:

SELECT DONORS.ACCTID, DONORS.TITLE, DONORS.FNAME, DONORS.LNAME, DONORS.ADDR1, DONORS.CITY, DONORS.STATE, DONORS.ZIP, DONORS.COUNTRY, DONORS.SUPPORT1, DONORS.EMAIL1, DONORS.EMAIL2, 
LEN(DONORS.SUPPORT1) as LenSUPPORT1, LEN(DONORS.EMAIL1) as LenEMAIL1, LEN(DONORS.EMAIL2) as LenEMAIL2
FROM DONORS
ORDER BY DONORS.ACCTID;

And locate a record which you think should be captured by the code we are trying to come up, and let me know what values the three new calculated fields at the end are returning.

[cshipley83]

For empty email fields
LenSUPPORT = 1
LenEMAIL1 = 0
LenEMAIL2 = this column is empty

For non empty email fields
LenSUPPORT = 1
LenEMAIL1 = 30
LenEMAIL2 = this column is empty

[JoeM]

OK, it appears that there is some sort of unexpected difference between EMAIL1 and EMAIL2.
Are the values coming from the same place?
Are the fields set up exactly the same (what is their Data Types)?

Let me know what this returns for empty email fields:

SELECT DONORS.ACCTID, DONORS.TITLE, DONORS.FNAME, DONORS.LNAME, DONORS.ADDR1, DONORS.CITY, DONORS.STATE, DONORS.ZIP, DONORS.COUNTRY, DONORS.SUPPORT1, DONORS.EMAIL1, DONORS.EMAIL2, 
LEN(DONORS.SUPPORT1) as LenSUPPORT1, LEN(DONORS.EMAIL1 & "X") as LenEMAIL1, LEN(DONORS.EMAIL2 & "X") as LenEMAIL2
FROM DONORS
ORDER BY DONORS.ACCTID;

[cshipley83]

Empty email fields
LenEMAIL1 = 1
LenEMAIL2 = 1

Note: All Email2 fields will be empty as no data has of yet been entered into them, but there may be in the future.

Data is from the same Donors Table
Data Type is Short Text

[JoeM]

OK, this workaround should give you what you want:

SELECT DONORS.ACCTID, DONORS.TITLE, DONORS.FNAME, DONORS.LNAME, DONORS.ADDR1, DONORS.CITY, DONORS.STATE, DONORS.ZIP, DONORS.COUNTRY, DONORS.SUPPORT1, DONORS.EMAIL1, DONORS.EMAIL2
FROM DONORS
WHERE (((DONORS.SUPPORT1)="X") AND (LEN(DONORS.EMAIL1 & "X")=1) AND (LEN(DONORS.EMAIL2 & "X")=1)
ORDER BY DONORS.ACCTID;

[cshipley83]

Alright, that seems to work! Thank you so much. Sorry it took so long.

[JoeM]

Glad we got it working out for you.

Blanks/nulls/empty/spaces sometimes can be problematic to work with.
Sometimes it likes "Is Null", other times "" will work. I am sure there is some rhyme or reason to it (if there, I cannot keep it straight!).
By adding a single character to the value and checking for a length of 1, that takes care of both situations in one fellow swoop.

[cshipley83]

It's so confusing, especially since I'm not an expert at this stuff.

Sorry, but I just realized that I also need these labels to not pull up any addresses that are blank. How do I add that in to your SQL formula?