03-31-2011, 07:01 AM #13 Brianwarnock Retired Join Date: Jun 2003 Location: Merseyside England Posts: 12,680 It would make the resulting reports neater to look at for my end users. Source I believe it can be used like so in conjunction with the iif function: Iif(IsError(Expresssion);0;Expression) Go to Solution 2 Participants Anders Ebro (Microsoft MVP) LVL 5 MS Access5 LillyC 2 Comments

Can you provide a sample in which IsError() returns a True/False in a Query Object? >> I also would agree that it would be better to avoid the problem in the #error In Access Report Join our community for more solutions or to ask questions. You can also verify my claim by using VB Script to execute a JET query with IIf() in it ...

For example, the nulls are counted in this kind of query: ALTER TABLE Table1 ADD CONSTRAINT chk1 CHECK (99 < (SELECT Count(*) FROM Table2 WHERE Table2.State <> 'TX')); Error 2:

IsErrorTest.mdb 0 LVL 11 Overall: Level 11 MS Access 11 Message Expert Comment by:datAdrenaline2009-03-30 Comment Utility Permalink(# a24021308) Hello Break40 Thanks for posting the SQL, however, that leads us to Delivered Fridays Subscribe Latest From Tech Pro Research Project prioritization tool: An automated workbook IT leader’s guide to the future of artificial intelligence System monitoring policy Hiring kit: Microsoft Power BI Some folks call this behavior a bug, I personally call it a feature simply because by bringing the expression to the top level, the expression is only evaluated when needed, thus Remove #error From Access Query On the records where there was no matching record, IsError() failed, AvoidError() worked.

Is this possible? 0 Comment Question by:LillyC Facebook Twitter LinkedIn Email https://www.experts-exchange.com/questions/28924829/Replace-Error-with-null-or-0-in-a-query-in-Access-2010.htmlcopy LVL 5 Active today Best Solution byAnders Ebro (Microsoft MVP) You could look into the IsError function. All rights reserved. For example: strName = Nz(Me.MiddleName, "") lngID = Nz(Me.ClientID, 0) Error 5: Comparing something to Null The expression: If [Surname] = Null Then is a nonsense that will never be True. have a peek here Texas, USA speed ticket as a European citizen, already left the country Could California Ratify the Paris Agreement?

Why is nuclear waste more dangerous than the original nuclear fuel? and always responds "How do I know whether your unknowns are equal?" This is Null propagation again: the result is neither True nor False, but Null. I've had a couple of programming dead lines the past few days, so it's been busy. We'll demonstrate how to do this with the example below.

There are a number of ways to avoid this. Full Bio Contact See all of Susan's content Google+ × Full Bio Susan Sales Harkins is an IT consultant, specializing in desktop solutions. I thought I would through in a characteric of JET/ACE that may help clarify some stuff ... Hahaha!

Is there any way to change this to display anything but #error. I do beleive that the >>I believe if you try it you will find that IsError() does work and will indicate True for any type of error that results from the when i pull up the report sometimes text values appear because of invalid values or calculations such as "#NUM!" AND "#DIV/0!". Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms