Access Form Field Displaying Error

Question

I have a field on a Form which sums the total amount from a Raw Data table.

If the Table is not loaded, the field displays an Error as per below:

I would like to get the field to show a zero if the table the formula in the field is referring to has not yet been loaded (instead of the error reference above).

So instead of using this formula for that cell =DSum("[Amount]","[Raw Data Table]")

I am trying to use the below: =IIf(IsError(DSum("[Amount]","[Raw Data Table]")),0,DSum("[Amount]","[Raw Data Table]"))

However this still gives me an Error in the field if the table is not loaded.

Could you please help?

Thanks

Gustav Gustav · Accepted Answer · 2017-02-08T11:01:56

You may have to use a helper function like this:

Public Function NzDSum(ByVal Field As String, ByVal Table As String) As Double

    Dim Result  As Double

    On Error Resume Next
    Result = DSum(Field, Table)

    NzDSum = Result

End Function

and then this expression:

=NzDSum("[Amount]","[Raw Data Table]")

Access Form Field Displaying Error

1 Answers