Solved

IsError

Posted on 2016-08-25
6
29 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
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 57
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now