none
Running total not calculating correctly RRS feed

  • Question

  • Hello,

    I have a query that calculates a running sum using a function inside the query. It works good and I've never had an issue with it until now and it's only happening on one record (that I can tell). The running sum should show a value of 0 (zero) because that is what the TotalLine sum adds up to (The TotalLine simply makes value positive or negative depending on type of transaction). However, I'm getting a return value of -1.06581410364015E-14.  All of the running sum values look good until the very last calculation.  I have tried compact and repair without success.

    See query and function below. Notice last row has incorrect running balance. It should be 0.


    <tfoot></tfoot>
    qryParticipantTransactions
    Credit Debit TotalLine RunninBalance
    1779
    1779 1779

    230 -230 1549
    10
    10 1559
    10
    10 1569

    25 -25 1544

    25 -25 1519

    25 -25 1494

    25 -25 1469
    10
    10 1479

    25 -25 1454

    25 -25 1429
    10
    10 1439
    10
    10 1449

    25 -25 1424

    25 -25 1399

    25 -25 1374

    25 -25 1349

    25 -25 1324

    25 -25 1299

    25 -25 1274

    25 -25 1249

    25 -25 1224

    25 -25 1199

    25 -25 1174

    25 -25 1149
    10
    10 1159

    25 -25 1134

    25 -25 1109
    10
    10 1119
    10
    10 1129
    10
    10 1139

    25 -25 1114

    25 -25 1089

    25 -25 1064

    25 -25 1039

    50 -50 989

    25 -25 964
    10
    10 974

    25 -25 949

    25 -25 924

    20 -20 904
    20
    20 924

    50 -50 874
    10
    10 884

    25 -25 859
    25
    25 884

    25 -25 859

    75 -75 784

    25 -25 759

    25 -25 734

    25 -25 709

    120 -120 589

    25 -25 564
    10
    10 574
    10
    10 584

    25 -25 559

    10 -10 549

    35 -35 514

    25 -25 489

    25 -25 464

    25 -25 439

    25 -25 414

    25 -25 389

    25 -25 364

    25 -25 339

    65 -65 274
    10
    10 284

    25 -25 259

    51.8 -51.8 207.2

    52 -52 155.2

    52 -52 103.2

    52 -52 51.2
    10
    10 61.2

    30 -30 31.2

    31.2 -31.2 -1.06581410364015E-14

    -

        

    Public Function fCalcBalRunningTotal(ParticipantID As Long, TransDate As Date) As Double

        fCalcBalRunningTotal = DSum("[TotalLine]", "qryParticipantTransactions", _
            "[ParticipantID] =" & ParticipantID _
            & " And [TransDate]<=#" & TransDate & "#")
    End Function

    Thanks in advance!!!


    Monday, October 5, 2015 5:11 AM

Answers

  • Public Function fCalcBalRunningTotal(ParticipantID As Long, TransDate As Date) As Double

        fCalcBalRunningTotal = DSum("[TotalLine]", "qryParticipantTransactions", _
            "[ParticipantID] =" & ParticipantID _
            & " And [TransDate]<=#" & TransDate & "#")
    End Function

    Hi azcactus,

    It is the consequence of the Double (or Single) datatype, with its natural inaccuracy.

    If you use the Currency type, you will get rid of an accuracy less then 10 exp(-4).

    Imb.

    Monday, October 5, 2015 7:35 AM