Expression Logic in Field Query

[Dave]

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

 

[Alex Ivanov]

It does not work because there is no records to return.
I would recommend to use a function instead of query. Assuming using DAO it
might look like this:

Public Function GetValue(SQL As String)
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(SQL)
If Not rs.EOF Then
GetValue = SQL(0)
Else
GetValue = Null 'or 0, if you prefer
End If
End Sub

HTH
Alex.