Display "0" if no records are present?

[mark909]

I have a query that calulates the length of an item if it is present within a
certain project. How ever if that item, "New Mini-Duct Cable" isnt present
the SumOfLINE_LENGTH gives a blank field. What I want the query to do is
display a 0 if no records of "New Mini-Duct Cable" are found in an individual
project. Any Ideas?


SELECT CABLE_LINE.PROJECT_ID, CABLE_LINE.CABLE_LINE_TYPE,
Sum(CABLE_LINE.LINE_LENGTH) AS SumOfLINE_LENGTH

FROM CABLE_LINE

GROUP BY CABLE_LINE.PROJECT_ID, CABLE_LINE.CABLE_LINE_TYPE
HAVING (((CABLE_LINE.PROJECT_ID)=Forms!frmBillOfMaterials!txtProject_ID) And
((CABLE_LINE.CABLE_LINE_TYPE)="New Mini-Duct Cable"));



Thanks for any help!

 

[RonaldoOneNil]

Not tested but try this,
.....
Nz(Sum(CABLE_LINE.LINE_LENGTH),0) AS SumOfLINE_LENGTH
....

 

[mark909]

Thanks for help but Nz function only works if there is a record with a null
value.

The problem is that sometimes when i query a project id there is no "New
Mini-Duct Cable" at all within the project.

So i need the query to give a result of "0" in LINE_LENGTH if no record is
present

 

[John Spencer]

Try using a sub-query in the SELECT clause to get the value

SELECT CABLE_LINE.PROJECT_ID
, CABLE_LINE.CABLE_LINE_TYPE
, Nz((SELECT Sum(TEMP.LINE_LENGTH) As length
FROM CABLE_LINE as TEMP
WHERE TEMP.Project_ID = Cable_Line.Project_ID
AND TEMP.CABLE_LINE_TYPE="New Mini-Duct Cable"),0) as LineLength
FROM CABLE_LINE
WHERE CABLE_LINE.PROJECT_ID=Forms!frmBillOfMaterials!txtProject_ID

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County