Display Something Other Than #Error In a Report Control

[tallbarb]

Some background: I am working with a main/subreport layout. The report control I'm referring to is really displaying the value of a control in the subreport.

The main report display totals (sum & count functions) at the bottom of each page (I have page breaks after each record). The problem is, when the sum or count has nothing TO sum or count, I get #Error in the control. How do I get something else to display - either a zero or a message that I provide?

I have a feeling this is an easy one but, for the life of me, I haven't been able to figure it out.

 

[FoxProProgrammer]

If the problem is being caused by a Null value, you could use the Nz function to display 0. You didn't say which version of Access you are using. The Nz function is only available in Access 2000 and above. dz
dzaccess@yahoo.com

 

[tallbarb]

Using Access 97 but could easily use 2000 instead.

Need a bit more clarification. Here's the expression for the report control that is displaying a value from a subreport control:

=[Reports]![Employee and training data]![training master]![CountClasses]

When there are no classes taken by an employee, I get an #Error. Since I don't know VBA, is there an expression I can build (either modifying the current one or creating a separate control that tests the results in this control)?

I appreciate your help!

 

[FoxProProgrammer]

You might try replacing your expression with the following:

=IIf(IsNull([Reports]![Employee and training data]![training master]![CountClasses]), 0, [Reports]![Employee and training data]![training master]![CountClasses])

I think that it will work, but haven't tested it.

[Reports]![Employee and training data]![training master]![CountClasses]
dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Please disregard the expression at the bottom of my post. I cut and pasted it to make sure that I wouldn't make a mistake typing it, and forgot to delete it before I submitted the post. dz
dzaccess@yahoo.com

 

[danvlas]

Try:

IIf(IsError(YourFullExpression),"Whatever",YourFullExpression)

Good luck.

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[lennyl]

To FoxProProgrammer

The NZ function is available in Access 97 not 2000 and above

Lenny

 

[danvlas]

Lenny: You're wrong! Nz is there as well. But check for missing references in your Acess 2K...When you restore them, you'll find the Nz function all right...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[tallbarb]

danvlas: That worked. THANK YOU!!!