Solved

IsError

Posted on 2016-08-25
6
51 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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