Crosstab Calculations, hide DIV/0 & ERROR messages

[sirkenj]

I have a report with the data source being a crosstab query. The crosstab is a "Count" of, and there are several rows / columns I have added (of text boxes) to perform calculations on these values. Since the crosstab displays nothing if the count is zero (instead of "0&quot, I am using the Iif funciton in the calculated text boxes. Like(Iif [#1]isnull,0,[#1])-(Iif [#2]isnull,0,[#2])). My calculations / percentages are all working fine, but I would like to display errors as either "0", or null. For instance, if [#1] is null, and [#2] is not, the percentage comes up with DIV/0. Is there a way to default this to either zero / null, or simply hide this type of error? Your help is greatly appreciated in advance.

 

[BVerlaan]

Hi Sirkenj,

I'm not sure if you can call a public function in a calculated field of a crosstab query, but it could be worth the try.
Building a public function would give you more control. Perhaps even if an error is raised you can give back a value to your query. The public function could look something like:

Public Function MyFunction (ByVal InputValue1 As . . ., ByVal InputValue2 As . . ., etc.) As Variant
On Error Goto Err_Handling

'Your calculations

Exit_MyFunction:
Exit Function
Err_Handling:
MyFunction = "the signal you'd like to see in the results"
Resume Exit_MyFunction
End Function

Perhaps MyFunctions shouldn't be of type Variant, but of type Decimal (or the same type as the calculated field of your query.

Good luck, Bart Verlaan.