NZ in a Count Query

[Dennis]

I have a simple count query that I would like to put a zero for a null value
for the count and also indicate the group that was counted. following is the
SQL:

SELECT [Table CCNPP Critical Spare Equip].Status, Count([Table CCNPP
Critical Spare Equip].UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY [Table CCNPP Critical Spare Equip].Status
HAVING ((([Table CCNPP Critical Spare Equip].Status)="PO"));

the result would be Status = PO and if null value for Count of UEI = 0 for
any null values.
Any help would be appreciated,

Thank you,

Dennis

 

[John Spencer]

NZ cannot help when no records are returned.

SELECT Status
, Count(UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY Status
HAVING Status = "PO"

That query can return 0 for the count as long as there is at least one record
with a Status of "PO". IF there is no record with a status of "PO" then no
record will be returned and therefore there can be no count returned.

You could use the DCount function instead which does work to return 0 even if
there are no matching records.

DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = ""PO""")

You can even use the DCOUNT in a query

SELECT DISTINCT [Input Status]
, DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = """ & [Input
Status] & """") as theCount
FROM [Table CCNPP Critical Spare Equip]



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

 

[Dennis]

John,

The DCount works, except that I have Value Box requesting for Input Status,
is there anyway to designate my status as "PO" or whatever in the Query
statement?

Thank you,

Dennis

NZ cannot help when no records are returned.

SELECT Status
, Count(UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY Status
HAVING Status = "PO"

That query can return 0 for the count as long as there is at least one record
with a Status of "PO". IF there is no record with a status of "PO" then no
record will be returned and therefore there can be no count returned.

You could use the DCount function instead which does work to return 0 even if
there are no matching records.

DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = ""PO""")

You can even use the DCOUNT in a query

SELECT DISTINCT [Input Status]
, DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = """ & [Input
Status] & """") as theCount
FROM [Table CCNPP Critical Spare Equip]



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

I have a simple count query that I would like to put a zero for a null value
for the count and also indicate the group that was counted. following is the
SQL:

SELECT [Table CCNPP Critical Spare Equip].Status, Count([Table CCNPP
Critical Spare Equip].UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY [Table CCNPP Critical Spare Equip].Status
HAVING ((([Table CCNPP Critical Spare Equip].Status)="PO"));

the result would be Status = PO and if null value for Count of UEI = 0 for
any null values.
Any help would be appreciated,

Thank you,

Dennis

 

[Dennis]

John,

I got it, again thanks for your help!
Have a Great Day! I am!

John,

The DCount works, except that I have Value Box requesting for Input Status,
is there anyway to designate my status as "PO" or whatever in the Query
statement?

Thank you,

Dennis

NZ cannot help when no records are returned.

SELECT Status
, Count(UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY Status
HAVING Status = "PO"

That query can return 0 for the count as long as there is at least one record
with a Status of "PO". IF there is no record with a status of "PO" then no
record will be returned and therefore there can be no count returned.

You could use the DCount function instead which does work to return 0 even if
there are no matching records.

DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = ""PO""")

You can even use the DCOUNT in a query

SELECT DISTINCT [Input Status]
, DCount("UEI","[Table CCNPP Critical Spare Equip]","Status = """ & [Input
Status] & """") as theCount
FROM [Table CCNPP Critical Spare Equip]



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

I have a simple count query that I would like to put a zero for a null value
for the count and also indicate the group that was counted. following is the
SQL:

SELECT [Table CCNPP Critical Spare Equip].Status, Count([Table CCNPP
Critical Spare Equip].UEI) AS CountOfUEI
FROM [Table CCNPP Critical Spare Equip]
GROUP BY [Table CCNPP Critical Spare Equip].Status
HAVING ((([Table CCNPP Critical Spare Equip].Status)="PO"));

the result would be Status = PO and if null value for Count of UEI = 0 for
any null values.
Any help would be appreciated,

Thank you,

Dennis