Overflow message.

[Robeen]

Hi,

MS Access 2010.

I have a report that runs off a query that has one field that lists percentages. They show up like 4.0730%. Some rows have no values in them.

The report runs fine till I try & click on the field and do a Sum or Average to create subtotal & grand total fields. Then I get the 'Overflow' message.

I'm pretty sure that I actually got this to work a few times - but now it will not.

There is another percentage field in the report that I am averaging - no probs.

I'd appreciate any advice!




TIA!

Robin

[NTC]

not sure if this is a technical issue or a math issue.

let's start with the math; one can't add/sum a % as it is not mathematically correct.
1 of 2 = 50%
1 of 3 = 33%
neither the sum nor the average of 50 & 33 are correct - -although the avg is closest - - one must actually calculate 2 0f 5 to get the correct avg.....

having said that - am not sure the software cares - maybe it does; have never checked.

I trust you have sanity checked setting up a sum/avg on a simple non-percentage number and everything is ok?

[Robeen]

Thanks, NTC.

I mentioned that I have another [identical data-wise] percent field on the report that I am using 'Average' on - and it works fine.

The difference between the two fields is that this one has rows where there are no values [a certain customer, for example, made no purchases in the time period that this column is reporting].

I'm working on controlling the data type of this field and using the Nz function on the values so there'll be 0's instead of Null or empty values - maybe that will help.

Still haven't been able to solve it though.

Robin

[SteveH2508]

If those Null/Zero values are used as a denominator in a fraction you will get an overflow or a divide by zero error. Hard to say without seeing the data and the report.

A nasty kludge might be to Nz those fields with 0.00000000001 instead of zero. (depends on your data and how accurate you need to be).

[NTC]

I would probably put those nz in the query record source itself so the report is receiving 0s rather than nulls..... and as SteveH pointed out a divide by 0 will make it barf.....

[Robeen]

I've been grappling with this all day and still get the overflow mgsbox - when I try & do a Sum on that 'Rate' value in the Report.

Here's the whole process this value is going through:
1. In the Table - there are 2 'Number' fields - Amount & Interest.
2. I run a query that Groups on Customers, Counts their Purchases/LoanAmounts, Sums their Balances, Sums the Interest paid on their Balances & Calculates Rate: ([PMInterest]/[PMBal])*100. This field in the query is a 'General Number', Decimal places 4. This query sorts for Previous Month transactions.
There are similar queries for YearToDate, PreviousMonth Last year etc . . .
3. Other queries return Customers, Balances & 'Rate' from the different Time periods. In this query, I have now got the 'Rate' for the PreviousMonth defined in Query View as 'General Number', Decimal Places 4.
Since some customers will not have any transactions in the prev month, I run the 'Rate' value through a function [within the query]:

 
Function Display_Average(Value_1) As Double
 
Dim First_Value As Double
Dim Result As String
 
First_Value = Nz(Value_1, 0) 
 
If First_Value > 0 Then
    Result = Format(((First_Value)), "0.0000")
Else
    Result = 0.0001
End If
 
Display_Average = Result
 
End Function

Then I run a query to combine all the values for Every Customer for every time period.
This gives me a resultset that lists EVERY Customer down the left - and then presents their transactions for the different Time Periods across the page.

5. The Report runs off the final Query & lists all customers grouped by City. The Summing is for each City.

6. No problem at all till I try & do a 'Sum' on this one 'Rate' field. That's when I get the Overflow. It is still defined as a 'General Number' - Decimal 4. I have summed the Rate fields from other time periods and they Sum just fine[They do not have any empty rows, though].

I'm not sure if I've been clear about this or if I've left out important details.

I'd appreciate any help anyone can give me!!!

Thanks!

Robin

[pbaldy]

The Format() function will cause the result to be a string rather than a number, which can cause problems when trying to perform math. You even declare the variable as a string. I might think that since the return value is Double it might force it back, but try declaring that as a double and instead of the Format() function just pass the value through. I might just do:

First_Value = Nz(Value_1, .0001)

[Robeen]

Thanks to everyone who had suggestions.

As this was on a pretty tight deadline, while I was waiting for suggestions, I just re-worked the one query that was giving me the headache.

I think what fixed it for me was getting rid of all the rows where there were no values. I just put " Where . . . > 0" in the queries leading up to the final query & then when I did the Sum - it worked like a charm. SO . . . I have to assume it was somethign to do with 0's or Nulls or Empty values somewhere.

Before I got a chance to try Paul's suggestion for my function, I had already got the query working without the function.

Also - I ended up doing the grouping in the component queries rather than
in the report.

Once again, thanks for the suggestions.

Robin