Hide #Error

[hobbit2612 via AccessMonster.com]

Hi,

I wonder if someone can help me please.

I have a text box on a form which is populated by data from a query.
Unfortunately, when there is no data the text box shows #Error.

What I would like to do is hide this value unless there is actually proper
data to show. Being a bit of a novice I naturally thought I could use
conditonal formatting, and I've tried various different ways of doing this
without success.

So I've done a bit of digging around on MS help and come up with the formula
below, but again unfortunately it doesn't work. The box remains blank whether
there is data or not.

=IIf(IsNull("Queryname"),0)

Could someone help me please?

Regards

Chris

 

[Clifford Bass]

Hi Chris,

What is the data that is supposed to show when there is a value? The
value from a table's column in the query? A calculated result in the query?
Or a calculated result in the form? If a calculated result, what is the
calculation and where is it located? Is the calculation related to rows in a
subform?

Clifford Bass

 

[hobbit2612 via AccessMonster.com]

Hi clifford,

Many thanks for replying.

The data in the text box is a currency amount. The query that feeds this, is
built into the form itself as a subform.

The data originates from a column in the table called 'Cost' and is summed in
the query before it is used in the text box.

Kind regards

Chris

Hi Chris,

What is the data that is supposed to show when there is a value? The
value from a table's column in the query? A calculated result in the query?
Or a calculated result in the form? If a calculated result, what is the
calculation and where is it located? Is the calculation related to rows in a
subform?

Clifford Bass

Hi,

[quoted text clipped - 19 lines]

Chris

 

[Clifford Bass]

Hi Chris,

Okay, I think I grasp that. You have a subform named say
"sfrmMySummary" and on it is a bound text box named something like
"txtSumOfCost". txtSumOfCost contains the sum of the cost calculated by a
summary query. In your main form you want to display that value; so you have
an unbound text box which references the bound text box in the subform. But
when there are no records you get #Error. And you would rather have it not
show anthing. In which case, this should do the trick:

=IIf(IsError([sfrmMySummary]![txtSumOfCost]),Null,[sfrmMySummary]![txtSumOfCost])

Clifford Bass

 

[hobbit2612 via AccessMonster.com]

Clifford,

You've got it spot on!

Many thanks for your time, trouble and help.

Kind Regards

Chris

Hi Chris,

Okay, I think I grasp that. You have a subform named say
"sfrmMySummary" and on it is a bound text box named something like
"txtSumOfCost". txtSumOfCost contains the sum of the cost calculated by a
summary query. In your main form you want to display that value; so you have
an unbound text box which references the bound text box in the subform. But
when there are no records you get #Error. And you would rather have it not
show anthing. In which case, this should do the trick:

=IIf(IsError([sfrmMySummary]![txtSumOfCost]),Null,[sfrmMySummary]![txtSumOfCost])

Clifford Bass

Hi clifford,

[quoted text clipped - 9 lines]

Chris

 

[Clifford Bass]

Hi Chris,

Glad to hear that was what was needed. You are welcome!

Clifford Bass