Field query expression

[Dave]

I mistakenly posted this in the DAO VBA area.

Maybe someone here can help:
My Field query expression is:
Expr1: IIf(IsNull((SELECT Sum(Kgs) AS SumOfKgs FROM
DailyProductShipments),0,(SELECT Sum(Kgs) AS SumOfKgs
FROM DailyProductShipments)

The reason I use IsNull is because I need this query to
return a "zero" if it finds no record (shipments for the
time period defined in DailyProductShipments). This query
properly returns a value for Sum(Kgs) as long as there are
one or more records. However, it returns no value if there
are no records. How can I get it to return a zero or is
there a beter way to do this?

Thanks in advance,
Dave

 

[fredg]

I mistakenly posted this in the DAO VBA area.

Maybe someone here can help:
My Field query expression is:
Expr1: IIf(IsNull((SELECT Sum(Kgs) AS SumOfKgs FROM
DailyProductShipments),0,(SELECT Sum(Kgs) AS SumOfKgs
FROM DailyProductShipments)

The reason I use IsNull is because I need this query to
return a "zero" if it finds no record (shipments for the
time period defined in DailyProductShipments). This query
properly returns a value for Sum(Kgs) as long as there are
one or more records. However, it returns no value if there
are no records. How can I get it to return a zero or is
there a beter way to do this?

Thanks in advance,
Dave

I'm not sure if this will work in this instance, but give it a try:

Expr1: Nz((SELECT Sum(Kgs) AS SumOfKgs FROM
DailyProductShipments,0)