Report and #Error

[wquatan]

Hi,

I have an Access2003 report based upon a query, which contains a field [Value]
In the footer I have a control : =Sum([Value])

Because the query returns sometimes no occurences, the control results in an #Error

To avoid that I modified the control into =IIf(IsError(Sum([Value]));0;Sum([Value]))

However the #Error remains

I also tried to do the IsError in a function, with the same result.

What am I missing/overlooking ?

Thanks

[Rabbit]

Hi,

I have an Access2003 report based upon a query, which contains a field [Value]
In the footer I have a control : =Sum([Value])

Because the query returns sometimes no occurences, the control results in an #Error

To avoid that I modified the control into =IIf(IsError(Sum([Value]));0;Sum([Value]))

However the #Error remains

I also tried to do the IsError in a function, with the same result.

What am I missing/overlooking ?

Thanks

If the record source of your report returns 0 records, all controls that use those fields in an expression will return #Error. Use the No Data event of the report to cancel opening the report or change the control source of the textbox.

[wquatan]

If the record source of your report returns 0 records, all controls that use those fields in an expression will return #Error. Use the No Data event of the report to cancel opening the report or change the control source of the textbox.

Problem is that the report (header/footer) must always be generated, even if no details are provided by the query. As such "cancel opening" is not an option.

What do you mean with "change the control source of the textbox" ? The sum is needed in case details are available.

Thanks for your help !

[Rabbit]

Yes but in case there are no details you need to set the control source to nothing so it doesn't show an error.

[wquatan]

Yes but in case there are no details you need to set the control source to nothing so it doesn't show an error.

How can I do this dynamically ?

No way to make the IsError working ? I thought this function is there to trap the #Error status.

Thanks

[Rabbit]

How can I do this dynamically ?

No way to make the IsError working ? I thought this function is there to trap the #Error status.

Thanks

IsError doesn't work for the aggregate functions in a report. You can use the DSum() function if you want.

Or, like I mentioned earlier, use the No Data event to change the control source to an empty string.

As a side note: In your iif function, you have to use commas, not semicolons. Fixing that won't make IsError work though.

[wquatan]

IsError doesn't work for the aggregate functions in a report. You can use the DSum() function if you want.

Or, like I mentioned earlier, use the No Data event to change the control source to an empty string.

As a side note: In your iif function, you have to use commas, not semicolons. Fixing that won't make IsError work though.

Ok, I'll give it a try tomorrow. It seems DSUM() might be what I need ! Any negative side effects (speed) ? I wonder if the query isn't re-executed for the DSUM()

Anyhow I'll have a look at NoData event too

The semicolom is related to the localisation we have.

Thank you !

[Rabbit]

Ok, I'll give it a try tomorrow. It seems DSUM() might be what I need ! Any negative side effects (speed) ? I wonder if the query isn't re-executed for the DSUM()

Anyhow I'll have a look at NoData event too

The semicolom is related to the localisation we have.

Thank you !

Yes, your query will effectively be run again for DSum. But if you don't have a whole lot of data and your database isn't on a network, the time required shouldn't be too overbearing. If it will be then you'll have to use the No Data event.

[wquatan]

Hi,

When I try to change the Control, at runtime I get an Error 2191 (can't set property in preview or when print has started) :

Private Sub Report_NoData(Cancel As Integer)
Me.TotalTransactions.ControlSource = 0
Me.StartAmountNextMonth.ControlSource = return_rptBaseValueDC()
End Sub

Thanks

[Rabbit]

I guess no data triggers too late in the process to change the property, you'll have to do it in the On Open event. Except now you'll have to check for no data first since you can't use that event anymore.