• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 63
  • Last Modified:

IsError

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
pdvsa
Asked:
pdvsa
1 Solution
 
Nick67Commented:
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
 
pdvsaProject financeAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
IsError is also an Access thing :-)
0
 
Nick67Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, I've used it successfully in many places.  I've also used Jim's legendary AvoidError function.
But, no worries.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now