How To Hide #error In Access Report
AccessObject TipsTablesQueriesFormsReportsMacrosQuick TipsNumbersQueriesVersion TipsAccess 97Access 2007-2010ResourcesTips via emailShare your secretsLearn VBAAccess to SQLWhats New! Hiding error messages VBA advantages -for greater functionality This tip looks at hiding error messages in a report, specifically the #error. Note: This tip is only required for Microsoft Access 2003 or earlier, as Microsoft Access 2007, 2010, automatically displays a blank field if there is no data, so no need for the following work around. Which is great. The #Error message in a report occurs when there are no underlying records in the table or query the report is based on (i.e. an empty recordset). The most likely field this will happen to is an unbound field that performs a calculation on another field, i.e. Sum, Average or Count. If there are no records to perform the calculation on then the #Error message is displayed. If you have many calculated unbound controls (fields), you will need to do this on each field. The reason for this is if the report comes accross one error, it stops calculating all the other fields and displays #error in all of them. So repeat the below for all calculated fields that may have a record count of 0. First you have to decided what you wish to display when hiding error messages (#error) You may with to display a 0, or leave the field blank if there is no data, as it makes the report a bit tidier, easier to read and more professional.One way of hiding the error message is to check whether the report has any data in it or not by evaluating the HasData property and then combining it with the IIF function. The HasData property returns one of three values:-1 = Bound report with records0 = Bound report with no records1 = Unbound reportThe example below shows how to use this in an unbound text box on a report that sums the field [Number1] in the report [Report1]. If there is data the sum of the field [Number1] is displayed, otherwise 0 is displayed. To set this up normally you would, add an unbound text box to the report and add the following syntax, usually in the header or footer: =Sum([Number1]) But if the field Number1 or some reason had no data you would just get the following diplayed. #error Therefore change the above to: =IIf([Report].[HasData],Sum([Number1]),0) This will overcome this problem. If you would like to learn more about the IIF function, you may wish to check out the following: IIf function - an introduction Nested IIF functions Too many Nested
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Access 2007 How to hide #error from showing up on form up vote 1 down vote favorite I have an expression: (Current Year / Last Year ) - http://www.simply-access.com/Hiding-Error-Messages.html 1 This gives me the current year's % change. If a new person joins, the database will not have data for Last Year, This causes an #Error to show up on the form for that field. I don't want this to show up is there anything I can do? ms-access ms-access-2007 share|improve this question edited Aug 2 '13 at 14:47 HansUp 79.2k114371 asked Aug 2 '13 at 14:36 Hold The Door 2611 add a comment| 1 Answer 1 http://stackoverflow.com/questions/18019622/access-2007-how-to-hide-error-from-showing-up-on-form active oldest votes up vote 2 down vote accepted Use an IIf expression in your query to return the calcalculed value when [Last Year] is not Null, and return Null when [Last Year] is Null. SELECT IIf(Nz([Last Year], 0) > 0, ([Current Year] / [Last Year]) - 1, Null ) AS percent_change FROM YourTable; share|improve this answer edited Aug 2 '13 at 16:59 answered Aug 2 '13 at 14:46 HansUp 79.2k114371 It's on a query. –Hold The Door Aug 2 '13 at 14:51 Works thank you! –Hold The Door Aug 2 '13 at 16:26 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not the answer you're looking for? Browse other questions tagged ms-access ms-access-2007 or ask your own question. asked 3 years ago viewed 988 times active 3 years ago Related 0Some access 2007 programming questions on forms2Passing Data Between Access 2007 Forms0Cancel update of single field on MS Access 2007 bound form1Access 2007 Add Record using same form2MS Access 2007: Find Where a Form / Query / Report is Used0Access 2007 - Update all rows in a
Custom Search UtterAccess Forums>Microsoft® Access>Access Forms How to hide #Error Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access http://www.utteraccess.com/forum/index.php?showtopic=1937812 Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- https://bytes.com/topic/access/answers/870546-access-report-hiding-field-label-when-field-null Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access how to Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web how to hide Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|-- Q & A - Access Wiki Options Drew2010View Member Profile Feb 18 2010, 12:56 PM Post#1Posts: 81Joined: 27-December 09I have a form with an unbound text box named "building"The control source of this text box is set to a dlookup function=DLookUp("building","building","id = " & [on_loc])until a number is entered into the on_loc text box.. the building text box displays "#Error"is there a quick and easy way of hiding this "#Error" message until or if someone makes an entry into "on_loc" text box?..Thanks,~Drew fkegleyView Member Profile Feb 18 2010, 01:15 PM Post#2UtterAccess VIPPost
Ask a Question Need help? Post your question and get tips & solutions from a community of 418,557 IT Pros & Developers. It's quick & easy. Access Report: Hiding field and Label when field is null P: 47 dantebothermy Hi all: On Access 2003: I have a tabular form with labels next to the associated fields. I'd like them both to dissappear when the associated field is null. ("Can Shrink" will do this for the field, but the label is still there, so the report does not shrink.) Any ideas how I can make the label shrink too? Thanks, Jun 15 '09 #1 Post Reply Share this Question 13 Replies Expert 100+ P: 1,287 ChipR Simply change the label to a text box with no backcolor, and set conditional formatting so that if the value is null ( or [control] = "" ), the text is the same color as the background. You will want to lock and disable the text box so the text can't be selected when it is not supposed to be visible. Jun 16 '09 #2 reply P: 47 dantebothermy Thanks. Actually, I found a different answer: I changed the label to a text box and set the control source to iif(isnull([related control]),null,"Label Title"). Then I set the Can Shrink properties for the label and the related to control to Yes. Jun 16 '09 #3 reply Expert 100+ P: 1,287 ChipR Thank you for sharing your solution. That's probably a much better idea, since I've experienced slow updating with conditional formatting. Jun 16 '09 #4 reply Expert Mod 15k+ P: 29,922 NeoPa Each section in a report has a Format event. You could handle this without any special usages of controls simply by setting the .Visible property of both controls to (Not IsNull(Me.Control)). Jun 17 '09 #5 reply Expert 100+ P: 1,287 ChipR Ah, I was thinking of continuous forms. Would the Format event work in that case? Forgive my laziness in not trying it myself, but I think it's quicker to ask. Jun 17 '09 #6 reply Expert Mod 15k+ P: 29,922 NeoPa @ChipR Not at all. Happy to answer. I believe the Format event is sp