Solved

IsError

Posted on 2016-08-25
6
54 Views
Last Modified: 2016-08-25
Hello, trying to display nothing or a 0 if there is a #error.  What is function in Access?  
=Iferror(DSum("Amount","tblDraws_Details1","DrawID1=" & [ID]),0)

thanks
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 41770681
IsError is an Excel thing.
The domain aggregates will return Null if there is no value returned.
You can catch that with Nz(SomeValueThatCanBeNull, TheSubstitute)

But #Error is going to show up if any of your VBA bombs and doesn't complete correctly.
The best way to avoid that is to create a VBA function that will set the value and catch any errors

Private Function MyNiceNonErrorValue As Long 'or integer or currency or whatever
dim final as As Long 'or integer or currency or whatever
'ok, what can go wrong
'what do I do if [ID] is not valid
'code
'valid id
final = DSum("Amount","tblDraws_Details1","DrawID1=" & [ID])
'what do I do if the DSum bombs
if final is null then final = 0
'etc
MyNiceNonErrorValue = final

End Function

And then you set your controlsource to =MyNiceNonErrorValue ()

But you won't ALWAYS escape #Error.
It is Access's way of letting you know something interfered with the evaluation of the control's value.
0
 

Author Closing Comment

by:pdvsa
ID: 41770688
Oh yes that Nz trick.  I forgot what that meant but get it now.  I think Nz is the solution.  

thank you for the explanation.
0
 
LVL 58
ID: 41770691
IsError doesn't always catch everything, so use this instead:

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

Jim.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 75
ID: 41770703
IsError is also an Access thing :-)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41770713
Ok @mx <grin>
"IsError is an Excel thing." should have been
IsError is more of an Excel thing.

You see it most often in Excel cell formulas where you don't want (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL) to show.

Access's is much more limited.  It has to be a numeric expression, and basically used in conjuection with CVErr, and it is not an analog for the Excel IsError at all.
0
 
LVL 75
ID: 41770765
Well, I've used it successfully in many places.  I've also used Jim's legendary AvoidError function.
But, no worries.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question