Form to return a zero

[Gismo]

Hi All, is it possible to return a zero when no records are found on a sum query instead of the form displaying a blank? I have a master parts table and a parts detail table. the form should display a zero for detail if no record is found but there is a master part available. the query runs a total stock on hand sum

 

[arnelgp]

Cast the calculated field to nz function,ie

Expr:nz(sum(ffieldname), 0)

Or

Expr: Cdbl(nz(sum(fieldname),0))

 

[Gismo]

Does this allow for another field to be added in the query? When I show the part number I get the warning you tried to execute a query that does not include the specified expression "PartNo" as pert of an aggregate function

 

[Minty]

You'll need to add the PartNo as a Group By field

 

[Gismo]

I do have the PartNo as group by now then I get Cannot have function in group by clause CDbl(nz(Sum([QTY]),0))

SELECT [SOH Serviceable].PartNo, CDbl(nz(Sum([QTY]),0)) AS Total
FROM [SOH Serviceable]
GROUP BY [SOH Serviceable].PartNo, CDbl(nz(Sum([QTY]),0));

 

[arnelgp]

Sorry you dint need ti use dbl()

sum(nz([qty],0))

 

[Minty]

You'll need a sub query to return all the parts from the Master Table, something like

SELECT MasterTable.PartNo,  Nz([SubQty],0) AS Stock
FROM MasterTable LEFT JOIN (SELECT [SOH Serviceable].PartNo, Sum([Qty]) AS SubQty
FROM [SOH Serviceable]
GROUP BY [SOH Serviceable].PartNo)  AS tCount ON MasterTable.PartNo = tCount.PartNo ;