Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

IsError

Posted on 2016-08-25
6
Medium Priority
?
55 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 2000 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

721 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