locked
Running total not calculating correctly RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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