Nz or Iif?

K

Kristibaer

I am creating a query and have a field that contains some null (blank)
values. I need a criteria code to populate the null/blank values with a
value (probably 1) so I can use the Count function to tell me how many of
these items have this blank field. Here is the SQL code I have. For this
example, I know that there are 9 records with null values for the serialno
field.

SELECT imserial.item, Count(imserial.serialno) AS CountOfserialno
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));


Thank you,
Kristi
 
J

John Spencer

SELECT imserial.item
, Count(IIF(imserial.serialno is Null, 1, Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

or

SELECT imserial.item
, Abs(Sum(imserial.serialno is Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

This works because the expression returns True (-1) or False (0). Summing
that will return a negative number which we use the Abs function to get rid of
the negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
K

Kristibaer

John:

Neither of these SQL statements populated the null fields with 1. This is
the criteria expreasion that it produced:

CountBlanks: Count(IIf(imserial.serialno Is Null,1,Null))

In similar queries I've done, I've used something like this:

Blank: (IIf([imserial].[serialno] Is Null,1))

However it doesn't seem to work in this case. Can you suggest another
criateria code?

Thanks,
Kristi
 
K

Kristibaer

John:

I massaged an old Nz statement that I've used and am getting closer. The
problem is that it seems I must keep the serialno field present to produce
the desired results. My end result should en up with 2 columns per record;
item and CountOfserialno only. Any suggestions on how to eliminate the other
column from the display results? Unchecking "Show" chagnes my display
results. Here's my currect SQL statement:

SELECT imserial.item, Count(imserial.serialno) AS CountOfserialno,
Nz([imserial].[serialno],1) AS Blank
FROM imserial
GROUP BY imserial.item, Nz([imserial].[serialno],1)
HAVING (((imserial.item) Like "B5G"));
 
J

John Spencer

I obviously don't understand what you are trying to do.

I thought you wanted a count of serialNo fields that were null where the item
was equal to B5G. The query should return 0 if none are null or a count of
the null Serialno otherwise.

It could return no record if there are no records with the Item B5G.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John:

Neither of these SQL statements populated the null fields with 1. This is
the criteria expreasion that it produced:

CountBlanks: Count(IIf(imserial.serialno Is Null,1,Null))

In similar queries I've done, I've used something like this:

Blank: (IIf([imserial].[serialno] Is Null,1))

However it doesn't seem to work in this case. Can you suggest another
criateria code?

Thanks,
Kristi


John Spencer said:
SELECT imserial.item
, Count(IIF(imserial.serialno is Null, 1, Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

or

SELECT imserial.item
, Abs(Sum(imserial.serialno is Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

This works because the expression returns True (-1) or False (0). Summing
that will return a negative number which we use the Abs function to get rid of
the negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
K

Kristibaer

My apologies, I thought I mentioned at the first message that I want a count
of how many records have null in serialno. In the case of item B5G, I know I
have 9 records with Null serialno, so my display should be B5G, 9.

John Spencer said:
I obviously don't understand what you are trying to do.

I thought you wanted a count of serialNo fields that were null where the item
was equal to B5G. The query should return 0 if none are null or a count of
the null Serialno otherwise.

It could return no record if there are no records with the Item B5G.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John:

Neither of these SQL statements populated the null fields with 1. This is
the criteria expreasion that it produced:

CountBlanks: Count(IIf(imserial.serialno Is Null,1,Null))

In similar queries I've done, I've used something like this:

Blank: (IIf([imserial].[serialno] Is Null,1))

However it doesn't seem to work in this case. Can you suggest another
criateria code?

Thanks,
Kristi


John Spencer said:
SELECT imserial.item
, Count(IIF(imserial.serialno is Null, 1, Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

or

SELECT imserial.item
, Abs(Sum(imserial.serialno is Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

This works because the expression returns True (-1) or False (0). Summing
that will return a negative number which we use the Abs function to get rid of
the negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Kristibaer wrote:
I am creating a query and have a field that contains some null (blank)
values. I need a criteria code to populate the null/blank values with a
value (probably 1) so I can use the Count function to tell me how many of
these items have this blank field. Here is the SQL code I have. For this
example, I know that there are 9 records with null values for the serialno
field.

SELECT imserial.item, Count(imserial.serialno) AS CountOfserialno
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));


Thank you,
Kristi
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

John's suggestion should return that... count of 9. I also fail to see why
you mentioned:

"Neither of these SQL statements populated the null fields with 1. "

It seems you are running two rabbits at the same time :) Either you
COUNT the nulls, either you change them into ones: use Nz(fieldName, 1).


Vanderghast, Access MVP


Kristibaer said:
My apologies, I thought I mentioned at the first message that I want a
count
of how many records have null in serialno. In the case of item B5G, I
know I
have 9 records with Null serialno, so my display should be B5G, 9.

John Spencer said:
I obviously don't understand what you are trying to do.

I thought you wanted a count of serialNo fields that were null where the
item
was equal to B5G. The query should return 0 if none are null or a count
of
the null Serialno otherwise.

It could return no record if there are no records with the Item B5G.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John:

Neither of these SQL statements populated the null fields with 1. This
is
the criteria expreasion that it produced:

CountBlanks: Count(IIf(imserial.serialno Is Null,1,Null))

In similar queries I've done, I've used something like this:

Blank: (IIf([imserial].[serialno] Is Null,1))

However it doesn't seem to work in this case. Can you suggest another
criateria code?

Thanks,
Kristi


:

SELECT imserial.item
, Count(IIF(imserial.serialno is Null, 1, Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

or

SELECT imserial.item
, Abs(Sum(imserial.serialno is Null)) AS CountBlanks
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));

This works because the expression returns True (-1) or False (0).
Summing
that will return a negative number which we use the Abs function to
get rid of
the negative.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Kristibaer wrote:
I am creating a query and have a field that contains some null
(blank)
values. I need a criteria code to populate the null/blank values
with a
value (probably 1) so I can use the Count function to tell me how
many of
these items have this blank field. Here is the SQL code I have. For
this
example, I know that there are 9 records with null values for the
serialno
field.

SELECT imserial.item, Count(imserial.serialno) AS CountOfserialno
FROM imserial
GROUP BY imserial.item
HAVING (((imserial.item) Like "B5G"));


Thank you,
Kristi
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz or Iif 8
Nz problem 9
parameter with iif statement in query criteria 8
Using NZ Function to return ALL records 4
Nz, IIF 2
NZ function 1
Nz function 0
Using IIF to count the is not null records in a table 8

Top