Remove #Error in Access 2007

vmccune
vmccune used Ask the Experts™
on
I want to remove the #ERROR from displaying in a field if the underlying data is null.  Is there a newer better way than an IIF and isnull?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Nz([field], [value if null])
will do the same thing.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
If this is based on a Function, declare the (missing) argument as a "Variant"

Author

Commented:
=nz([frmPrePlan20Buy].[Form]![sCYmDMDDOLLARS],Null)
=nz([frmPrePlan20Buy].[Form]![sCYmDMDDOLLARS],0)

both of these have no effect.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
not based on a function.  Just a total field from a datasheet.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Not sure where/how your using this, but IsNull won't work because with #Error, nothing is returned.

In some cases, you can get away with IsError(), but I've always used AvoidError() (see the following):

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26219691.html#a32865042

  You can also depending on the situation, use IIF() to check for a record count and if 0, display something else.

Jim.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
BTW, if you want to know some nitty gritty details, check out this old thread:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24272099.html

Jim.

Author

Commented:
ISError will get my by on this but the avoidError is very interesting.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial