Sum Not Accurate

The fact that using a while loop to iterate over the records also gives the wrong answer suggests that perhaps the problem is not with quick reports.

Can you post the code you use to sum the records with a while loop? Also, what do you mean when you say"4p" as a measure of the incorrectness of the sum?

Cheers,

Raymond.

Hi Raymond,

When I say 4p I mean the sum should be £234.66 but somtimes
it can be £234.62.

This is another problem not the same as above.
The sum function in quick reports is also incorrect I
use a expression component and just use Sum(tbOrders.Total)
but this is incorrect.

Here is the code for the while loop.

Var
 PrevRecord: TBookmark;
 Total: Extended;
begin
PrevRecord := PayTable.GetBookmark;
PayTable.DisableControls;
Total := 0;
 With PayTable Do Begin
  try
    PayTable.First;
    while Not PayTable.EOF do
    begin
   Total:= Total + PaytableTotal.Value;
  PayTable.Next;
  end
  finally
  PayTable.GoToBookmark(PrevRecord);
  PayTable.FreeBookmark(PrevRecord);
  PayTable.EnableControls;
  end;
end;

Asw

Is the table filtered? Is the filter set to what you want or do you want all records (in which case you should make sure the table is NOT filtered) ?

For that kinda stuff, I personally prefer to use SQL queries:

var
  Total : double;
begin
  Query1.SQL.Text := 'Select SUM(PayableTotal) as PTotal from PayTable';
  Query1.Open;
  Total := Query1.fieldbyname('PTotal').asfloat;
end;

That's much faster than interacting through all the records on a WHILE..DO loop.

By the way, unless you're working with extremely large values, use the "Double" type. It generates much better and faster code on x86 platforms.

Yours,

Alex

asw: How many items do you sum up? If it is a large number, perhaps you are getting some sort of rounding error creeping in... I too would normally use an SQL query to do the job - but its interesting that you get a wrong result using the while loop (which reminds me, how do you know the sum is wrong?)

alex: I am a little surprised about your comment re: doubles. Remember the doubles need to be converted to extended for use in the FPU, so efficiences regards memory moves can tend to be offset by this... Though you are right, extended in this instance is overdoing it!

Cheers,

Raymond.

Because it is quite a small difference (4p) it may be the result of rounding errors.

Check the actual values stored in your database to make sure they don't contain fractional pence.

What type of field are you using (Float or Currency or ?)

How many records are involved in the sum (roughly)?

My boss had a similar problem just the other day ....
"I've found a bug in Excel!!!" he exclaimed.
It turned out that he had values showing to two decimals, but the actual values had more significant digits and thus the sum was out.

Anyway that's my guess.

Hi Guy's

I think Hanlet is in the right direction, which is why
the quick reports sum is also incorrect.

I am using a paradox table the Paytotal is calculated

Var
Rate : Real;// Vat rate is 17.50%
begin
Rate := VatTableVatRate.Value;
PayTableVat.Value := PayTablePrice.Value * Rate / 100;
PayTableTotal.Value := PayTablePrice.Value + PayTableVat.Value;

In which case even though the field PaytableTotal is showing
£52.88 when I go into  Database desktop the field is showing
the same figure, but when I click on the £52.88 it changes to £52.875. So really the calculations are correct but on the £52.875.

I need to calculate the figure shown in the field £52.88 is this a bug or somthing.

Asw

Sorry I missed your question Hamlet,

I am using a currency Field.

Asw

Hi Guy's,

Thanks for your time Alex, Raymond but I need to give the points to Hamlet

Hamlet, if you put a few lines I will accept it as the answer and award the points.

Seems like Borland has some calculating coding to do.

I got around the problem using the round function.


Var
Rate, Vat: Real;
begin
Rate := VatTableVatRate.Value;
Vat := (PayTablePrice.Value * Rate / 100);
PayTableVat.Value := round(VAT*100)/100; //Gives  two decimal places.
PayTableTotal.Value := (PayTablePrice.Value + PayTableVat.Value);

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
https://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange