 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2007How can I suppress #Error message
I have a report in which there is a text box joining two names into one string. When the names are nothing, the text box shows #Error in the report. How can I suppress this so that only a space, or nothing, appears in the report. thanks ... Roger Use IIF statements -- IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "", [Field2])  SignatureKARL DEWEY Build a little - Test a little > I have a report in which there is a text box joining two names into one > string. When the names are nothing, the text box shows #Error in the report. > How can I suppress this so that only a space, or nothing, appears in the > report. > > thanks ... Roger Karl taking you at what you say, I did a little and tested a little. I have Access 2002 and I am talking about a report and the control source in a text box. I put in =IIF([Name1] Is Null, "", [name1]) and still got #Error. I also tried the =Nz[Name1] and also got #Error ??? any ideas ??? ... Roger > Use IIF statements -- > IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "", [quoted text clipped - 7 lines] >> >> thanks ... Roger Just to add to the puzzle, if I put =IIF ([Name1] Is Null,1,2) I get a 2 with no #Error message. But when I check back with the Query from where the data comes, there is definitely no data ? Roger > Use IIF statements -- > IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "", [quoted text clipped - 7 lines] >> >> thanks ... Roger Did you even TRY the Nz function like I suggested? Also, make sure your text boxes are named differently than your field names. SignatureBob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com If my post was helpful to you, please rate the post. __________________________________ > Just to add to the puzzle, if I put =IIF ([Name1] Is Null,1,2) I get a 2 > with no #Error message. But when I check back with the Query from where the [quoted text clipped - 13 lines] > >> > >> thanks ... Roger Yes, I tried Nz([Name1],""), in the control source but got a syntax error, so added the = to get =Nz([Name1],"") . But still get #Error. I am trying with just one field in the text box right now to make it simpler ! The text box name is a generic name (text44) whereas the field name is Name1. This is all in the report which gets its data from a query. When I look at the query there is no data, so the field in the report should be a nothing, since there is no Name1 data (and no records at all) in the query result.The odd thing is that if I try the iif statement, the field shows as having some data there, even though there isn't ? Roger > Did you even TRY the Nz function like I suggested? Also, make sure your text > boxes are named differently than your field names. [quoted text clipped - 16 lines] >> >> >> >> thanks ... Roger You can use the NZ function to handle nulls: Nz([YourField1],"") & " " & Nz([YourField2],"") SignatureBob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com If my post was helpful to you, please rate the post. __________________________________ > I have a report in which there is a text box joining two names into one > string. When the names are nothing, the text box shows #Error in the report. > How can I suppress this so that only a space, or nothing, appears in the > report. > > thanks ... Roger First, thanks for the help and advice. I have come to the conclusion that two possible options to fix this problem ... being a macro to reset value of text box to " " either ON ERROR or ON NO DATA (in the report properties) don't work .. at least I can't figure it out this way. The problem seems to be in the movement of the data from the qry to the report. So I have combined the data (ie the first, second, and third christian name) into one expression at the query instead of in the report. This seems to avoid the error message I've been getting in the report. again, thanks ... Roger >I have a report in which there is a text box joining two names into one >string. When the names are nothing, the text box shows #Error in the >report. How can I suppress this so that only a space, or nothing, appears >in the report. > > thanks ... Roger |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.