MS Access Query - Using Nulls

why do you think the query is not giving the correct records?
- your first query will return all records that have fields orgName, firstName and lastName filled with values
orgName      firstName          lastName
A                    john                    doe                     << satisfy the where condition
                       john                    doe                    << does not satisfy the where condition

what do you think?

My guess is that in your first query, you want to use OR instead of AND, try this query:

SELECT IsNull(USER_dbo_ams_contact.orgName) as OrgNameNULL
, IsNull(USER_dbo_ams_contact.firstName) as FirstNameNULL
, IsNull(USER_dbo_ams_contact.lastName) as LastNameNull
, Count(*) as RecCount
FROM yourTable
GROUP BY IsNull(USER_dbo_ams_contact.orgName)
, IsNull(USER_dbo_ams_contact.firstName)
, IsNull(USER_dbo_ams_contact.lastName)

This will give you a series of records which identify the number of records where each of those fields is null, it could look something like

OrgNameNull     FirstNameNull      LastNameNull    RecCount
    0                               0                                 0                      25
    1                               0                                 0                      15
    0                               1                                 0                         X
    1                               1                                 0                        Y
    1                                0                                1                        Z
    1                               1                                 1                        A

With this, you will be able to determine why the two queries do not appear to have the correct number of records.

But yet when I run the second query it gives me only 14 record where all 3 conditions are null.  So if I do the reverse should it give me 14 records less?

If I don't use the Where clause I get:
12,433
If I put in the where clause I get:
11,460

Should I not get:
12,433
-       14
12,421

Please help and thanks....

<Should I not get:
12,433
-       14
12,421 >

NOT necessarily..

your second query will return all records where the fields orgName, firstName and lastName have NO values

orgName      firstName          lastName
NULL             NULL                   NULL                     << satisfy the where condition
                       john                    doe                    << does not satisfy the where condition

Great thanks

First of all: Use table alias names.

Then post complete examples.

so you're trying to compare:

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NOT NULL
        AND C.firstName IS NOT NULL
        AND C.lastName IS NOT NULL;

-- vs. 

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NULL
        AND C.firstName IS NULL
        AND C.lastName IS NULL;

Select allOpen in new window

?

You're also aware of the rules of Boolean Distributivity of the Negation:

-- The Negation of

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NULL
        AND C.firstName IS NULL
        AND C.lastName IS NULL;

-- is

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   NOT ( C.orgName IS NULL
              AND C.firstName IS NULL
              AND C.lastName IS NULL
            );

-- or 

SELECT  *
FROM    USER_dbo_ams_contact C
WHERE   C.orgName IS NOT NULL
        OR C.firstName IS NOT NULL
        OR C.lastName IS NOT NULL;

Select allOpen in new window

Just to make it simpler I would write the negation of the expression:

 ([X] Is NULL) AND ([Y] IS NULL) AND ([Z] IS NULL)

this way:
NOT ([X] Is NULL) AND ([Y] IS NULL) AND ([Z] IS NULL)