#Error on a Report

[David Davis]

Hello,

I need help on getting rid of #Error in my fields on a report when no data exist. I was using the below formula:

="" & [System] //is a field of text type from table1

and when #Error showed I switched over to

=IIf(IsNull([System])," ",[System])

and that came up with the same results. If data exist it works fine but when data is not there I want it to be blank or say "No data exist".

What formula can I use in the control source on this report to fix this. Any assistance would be most appreciative.

Thanks

[Stewart Ross]

Hi David. You have double-posted a thread on the same topic, which is not appropriate nor courteous to the respondents (see http://www.thescripts.com/forum/thread783265.html).

If you are using a calculated control on a report make sure that its name is not set to be the same as the field you are referring to. You mention [system], so make sure the calculated field's name is not also system. You will undoubtedly receive a #Error if you try to refer in a calculated control to a field name which is the same as the control itself - it's a circular reference. Name the calculated textbox something else such as TheSystem or whatever you think is appropriate, just make sure it is a different name from that of the field you are testing.

In its control source property just enter =Nz([system]) to return a blank if the system field is null.

If you want to return a more specific message, then change this to
=Nz([system], "Field is null") or whatever suits.

-Stewart

[David Davis]

Hi David. You have double-posted a thread on the same topic, which is not appropriate nor courteous to the respondents (see http://www.thescripts.com/forum/thread783265.html).

If you are using a calculated control on a report make sure that its name is not set to be the same as the field you are referring to. You mention [system], so make sure the calculated field's name is not also system. You will undoubtedly receive a #Error if you try to refer in a calculated control to a field name which is the same as the control itself - it's a circular reference. Name the calculated textbox something else such as TheSystem or whatever you think is appropriate, just make sure it is a different name from that of the field you are testing.

In its control source property just enter =Nz([system]) to return a blank if the system field is null.

If you want to return a more specific message, then change this to
=Nz([system], "Field is null") or whatever suits.

-Stewart

Stewart,

No names are the same but I did use your code but it still produces #Error. Can't figure it out. It works great if data is there with any of my codes as well as the one you gave me but with no data it continues to come up with #Error in the text box. Any other things that could cause this?

David

[Stewart Ross]

Hi David. As NeoPa said in the other thread, there is something else amiss here. Nulls cannot of themselves cause #Error results in a report. There are some circumstances in which it can occur: when the field cannot be evaluated (as a result of a circular reference, or some other issue with the expression for that field), or when there is an illegal action such as division by zero taking place either directly within the expression for that item on the report, or indirectly within any of the queries feeding in to the report's source. Without knowing more about your report and the source query I would only be guessing if I tried to be more specific.

-Stewart

ps I see from the other thread that in fact it was an empty dataset problem - no records to report. From your attempts to test for nulls on individual fields it had seemed to me that you had #Error occurring on some, but not all, fields - how wrong to interpret it that way.

I chose not to delete this thread when I noted the double-posting; I would advise that the thread should now be treatead as closed, however!