How can I suppress #Error message

[Ro477]

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 DEWEY]

Use IIF statements --
IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "",
[Field2])

 

[boblarson]

You can use the NZ function to handle nulls:

Nz([YourField1],"") & " " & Nz([YourField2],"")
--
Bob 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.
__________________________________

 

[Ro477]

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, "",
[Field2])

--
KARL 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

 

[Ro477]

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, "",
[Field2])

--
KARL 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

 

[boblarson]

Did you even TRY the Nz function like I suggested? Also, make sure your text
boxes are named differently than your field names.

--
Bob 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
data comes, there is definitely no data ?



Roger

Use IIF statements --
IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "",
[Field2])

--
KARL 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

 

[Ro477]

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.

--
Bob 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
data comes, there is definitely no data ?



Roger

Use IIF statements --
IIF([Field1] Is Null, "", [Field1]) & " - " & IIF([Field2] Is Null, "",
[Field2])

--
KARL 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

 

[Ro477]

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