#error in Access

Question

I am running a report that attempts to manipulate data produced by a query. When the query does not return any data, my report fileds that perform calculations show #Error. Is there a way to supress that result so that either nothing comes up or the fields show blank? Thank you.

Answer 1

You'll get an error if you try to reference a control on a subform without any records.  Try the IsError function:

IIf(IsError([MyField]),0,[MyField]).

Answer 2

Thanks Scott. I used the IsError solution on the report because of the number of calculations involved. In this case I would have needed to create too many query calculations, whereas they were already in the reports (for the most part). Thanks again.

Answer 3

You may also be better off trying to include that field in your report's underlying query, rather than doing it directly on the Report. This is generally more performant, and will eliminate the #Error issue. To do that, just add a new Column to your query and use your IIF construct. In the Name row of a blank column:

MyNewField: IIf(#10/1/2010#=[date1],"IN",IIf(#10/1/2010# Between [date1] And [date2],"ON")))

Now your query will have a new field named "MyNewField", and you can include that on your report.

Answer 4

Bill, I am working with some dates in this manner.

The query calculates this for an October report-

for a field called [date1], the query specifies <=#10/30/2010# in criteria

for a field called [date2], the query specifies ,>=#10/1/2010# in criteria

As a result, if there is a date in from another month, it isn't shown in the results.

The report wants to use query with this expression -

= IIf(#10/1/2010#=[date1],"IN",IIf(#10/1/2010# Between [date1] And [date2],"ON")))

When there is no data, the report shows #error in that field.

Any thoughts?

Answer 5

You can check the value of the field used in the calculation and substitute a logical value if it is null.

For instance using this as a ControlSource:

=IIf(IsNull(MyField) = False, MyField * 100, 0)

The IIF() function takes 3 arguments: the expression to test, the return if true, and the return if false.

Capeesh?

---

Bill Mosca, MS Access MVP

http://www.thatlldoit.com

http://mvp.support.microsoft.com/profile/Bill.Mosca

http://tech.groups.yahoo.com/group/MS_Access_Professionals