AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Friday, December 12, 2008

How to avoid #Error in expressions

If you have a form with subform, last one has no records, and you have a textbox on a main form which calculates total – then you can get #Error as a result of your calculation.

Error

You can simply avoid this using IsError() function, just make the expression like this:

=IIf(IsError([frmMySubform].[Form]![txtTotal]),0, [frmMySubform].[Form]![txtTotal])

Labels:

4 Comments:

Blogger Greg said...

Interesting tip. I've seen this before, Greg

10:18 PM  
Anonymous Data Entry said...

This is a helpful one Alex. Thanks.

2:26 PM  
Anonymous Jim Dettman said...

I've been using the following functions since A2 days

Jim Dettman

Function AvoidError(n As Variant, varReplaceWith As Variant)

On Error GoTo AvoidError_Error

AvoidError = Nz(n, varReplaceWith)

AvoidError_Exit:
Exit Function

AvoidError_Error:
AvoidError = varReplaceWith
Resume AvoidError_Exit

End Function

9:49 PM  
Blogger grovelli said...

So the control source of the subform textbox would look like this if, for example, you wanted to sum one of the subform field values:
=sum(AvoidError([amount], "Replacement Value"))

11:17 AM  

Post a Comment

Links to this post:

Create a Link

<< Home