Fixing #Num! error and rounding when Inserting text box with formula

[Jerseynjphillypa]

Here is the formula that I inserted within a text box to do some calculation for me

=Sum([Used])/Sum([Total])*100

How would I remove the #Num! error

Also how can I round to 2 decimal places, for example 94.7368421052632 to 94.74.

[jzwp11]

You will have to provide some additional details about your form and your tables. Is the form on which the texbox is located a bound form (i.e. bound to a table or query)? What other controls do you have on the form? Where are you putting the text box on the form-in the detail section or the form footer? From where is the [Total] value coming, another table?

[Bob Fitz]

Here is the formula that I inserted within a text box to do some calculation for me

=Sum([Used])/Sum([Total])*100

How would I remove the #Num! error

What are you tring to calculate. Just the current record, or all records?

Also how can I round to 2 decimal places, for example 94.7368421052632 to 94.74.

Set the Format property to Fixed
Set the Decimal Places property to 2

[Jerseynjphillypa]

You will have to provide some additional details about your form and your tables. Is the form on which the texbox is located a bound form (i.e. bound to a table or query)? What other controls do you have on the form? Where are you putting the text box on the form-in the detail section or the form footer? From where is the [Total] value coming, another table?

My report is coming from one table I created using a Query. I used the Report Wizard, and placed the the text box with my formula in the Footer Section.

[Jerseynjphillypa]

What are you tring to calculate. Just the current record, or all records?



Set the Format property to Fixed
Set the Decimal Places property to 2

I am trying to calculate the average based on all the records that have the same item name from my table.


Also thanks for helping with my formatting issue with the decimal places.

[jzwp11]

If you are trying to calculate the average based on the item name, it sounds like you need to add a group level based on item name to your report. I created a real simple database with a report using the report wizard; please see the attached database.

[Jerseynjphillypa]

Thanks for the the jzwp11, the grouping helped
But sometimes the field is blank and there is no data for the item. Hence i get the error #N/A!. I want to know how I can remove this error.

[jzwp11]

You can filter the query on which the report is based to only show items that have the data. What is the SQL text of your query?

[Jerseynjphillypa]

I can provide you the query but when I created the query blank and 0 can be valid answer in the table. So that's why i am trying to figure out a way to remove the #Num error within the report section only

[AccessMSSQL]

In your query you can use the function =nz(fieldname). nz converts null values to zeros in the result.

[jzwp11]

I did not ask before, buy where are you getting the #Num and or #N/A? In one of the summary controls in the group footer or in the detail section of the report?

[AccessMSSQL]

To avoid divide by zero errors you need to do something like this:
=Iif (nz(Sum([Total])) = 0,0, Sum([Used])/Sum([Total])*100)

[bsftms]

That's because there's a Null value or a formula that result in null (error)
So, I prefer using solution below :
1. Make new module
2. Make a custom public function, use this code :

Public Function CustomDevide(ByVal x As Variant, ByVal y As Variant) As Variant
On Error GoTo ErrorHandler
Dim tmp As Variant
tmp = x / y
If IsNull(tmp) Then tmp = 0
CustomDevide= tmp
ErrorExit:
Exit Function
ErrorHandler:
CustomDevide = 0
Resume ErrorExit
End Function

3. use it as formula in your report like this

=CustomDevide(Sum([Used]);Sum([Total]))*100
4. That's it, sorry for my terrible english.