Why am I still getting data???

[Guest]

I created the below query to find out from which employees I am missing
information. I thought this would create a list of only employees with any
of these listed fields empty. However, my results will list everyone. Did I
do something wrong?
*************************
SELECT [Master List - New].[First Name], [Master List - New].[Last Name],
[Master List - New].Email,

IIf(IsNull([Home Address]),"Home Address" & Chr(13) & Chr(10)) &
IIf(IsNull([Home City]),"Home City" & Chr(13) & Chr(10)) & IIf(IsNull([Home
State]),"Home State" & Chr(13) & Chr(10)) & IIf(IsNull([Home Zip]),"Home Zip"
& Chr(13) & Chr(10)) & IIf(IsNull([Birthday]),"Birthday" & Chr(13) & Chr(10))
& IIf(IsNull([Cell]),"Cell" & Chr(13) & Chr(10)) & IIf(IsNull([Home
Phone]),"Home Phone" & Chr(13) & Chr(10))

AS [Personal Information]

FROM [Master List - New];

Results: (last name and email Intentionally left blank for posting)
First Name Last Name Email Personal Information
Anna
Riley
Paul
Erin Home Address

Home City

Home State

Home Zip

Birthday

Cell

Home Phone

Expected Results: (No Anna, Riley, or Paul)
Erin Home Address

Home City

Home State

Home Zip

Birthday

Cell

Home Phone

Thanks for any help.

 

[Smartin]

I created the below query to find out from which employees I am missing
information. I thought this would create a list of only employees with any
of these listed fields empty. However, my results will list everyone. Did I
do something wrong?
*************************
SELECT [Master List - New].[First Name], [Master List - New].[Last Name],
[Master List - New].Email,

IIf(IsNull([Home Address]),"Home Address" & Chr(13) & Chr(10)) &
IIf(IsNull([Home City]),"Home City" & Chr(13) & Chr(10)) & IIf(IsNull([Home
State]),"Home State" & Chr(13) & Chr(10)) & IIf(IsNull([Home Zip]),"Home Zip"
& Chr(13) & Chr(10)) & IIf(IsNull([Birthday]),"Birthday" & Chr(13) & Chr(10))
& IIf(IsNull([Cell]),"Cell" & Chr(13) & Chr(10)) & IIf(IsNull([Home
Phone]),"Home Phone" & Chr(13) & Chr(10))

AS [Personal Information]

FROM [Master List - New];

Hello,

Your query does nothing to include or exclude anything; it merely builds
a string and displays the result. Using "&" ensures a Null value will
not convert the entire concatenated result to a Null.

Alternatively, you could list the fields you want to see in the SELECT
clause (probably everything) and put the Null condition in a WHERE
clause. Since you want to test several fields and display the employees
where any one field is Null, use the + operator, which will propagate a
Null value.

SELECT [Master List - New].[First Name], [Master List - New].[Last Name],
[Master List - New].Email,
[Home Address], [Home City], [Home State], [Home Zip], [Birthday],
[Cell], [Home Phone]

FROM [Master List - New]

WHERE
([Home Address] + [Home City] + [Home State] + [Home Zip] + [Birthday]
+ [Cell] + [Home Phone]) Is Null
;

Hoping this helps,

 

[John W. Vinson]

I created the below query to find out from which employees I am missing
information. I thought this would create a list of only employees with any
of these listed fields empty. However, my results will list everyone. Did I
do something wrong?

Yes. You didn't apply any Criteria - shown on the grid by something on the
Criteria line, and in SQL in the WHERE clause. Your query will return all
records, whether or not they have data.

What records do you WANT to see? Those who have all these fields missing? or
those who have any one (or more) of the fields missing? If the latter, try

SELECT [Last Name] & ", " & [First Name] AS Who,
IIf(IsNull([Home Address]),"Home Address" & Chr(13) & Chr(10)) &
IIf(IsNull([Home City]),"Home City" & Chr(13) & Chr(10)) & IIf(IsNull([Home
State]),"Home State" & Chr(13) & Chr(10)) & IIf(IsNull([Home Zip]),"Home Zip"
& Chr(13) & Chr(10)) & IIf(IsNull([Birthday]),"Birthday" & Chr(13) & Chr(10))
& IIf(IsNull([Cell]),"Cell" & Chr(13) & Chr(10)) & IIf(IsNull([Home
Phone]),"Home Phone" & Chr(13) & Chr(10))

AS [Missing Information]

FROM [Master List - New]

WHERE [Home Address] IS NULL
OR [Home City] IS NULL
OR [Home State] IS NULL
OR [Home Zip] IS NULL
OR [Birthday] IS NULL
OR [Cell] IS NULL
OR [Home Phone] IS NULL;

John W. Vinson [MVP]

 

[Guest]

I had a little trouble converting to SQL, but using the Design Grid I was
able to get this to work. Thanks!!!!

I created the below query to find out from which employees I am missing
information. I thought this would create a list of only employees with any
of these listed fields empty. However, my results will list everyone. Did I
do something wrong?
*************************
SELECT [Master List - New].[First Name], [Master List - New].[Last Name],
[Master List - New].Email,

IIf(IsNull([Home Address]),"Home Address" & Chr(13) & Chr(10)) &
IIf(IsNull([Home City]),"Home City" & Chr(13) & Chr(10)) & IIf(IsNull([Home
State]),"Home State" & Chr(13) & Chr(10)) & IIf(IsNull([Home Zip]),"Home Zip"
& Chr(13) & Chr(10)) & IIf(IsNull([Birthday]),"Birthday" & Chr(13) & Chr(10))
& IIf(IsNull([Cell]),"Cell" & Chr(13) & Chr(10)) & IIf(IsNull([Home
Phone]),"Home Phone" & Chr(13) & Chr(10))

AS [Personal Information]

FROM [Master List - New];

Hello,

Your query does nothing to include or exclude anything; it merely builds
a string and displays the result. Using "&" ensures a Null value will
not convert the entire concatenated result to a Null.

Alternatively, you could list the fields you want to see in the SELECT
clause (probably everything) and put the Null condition in a WHERE
clause. Since you want to test several fields and display the employees
where any one field is Null, use the + operator, which will propagate a
Null value.

SELECT [Master List - New].[First Name], [Master List - New].[Last Name],
[Master List - New].Email,
[Home Address], [Home City], [Home State], [Home Zip], [Birthday],
[Cell], [Home Phone]

FROM [Master List - New]

WHERE
([Home Address] + [Home City] + [Home State] + [Home Zip] + [Birthday]
+ [Cell] + [Home Phone]) Is Null
;

Hoping this helps,