Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 13, 2024
Solved

Dsum

Hello experts,  I need to make a running sum total on tblDrawsDetails.Amount but noting the where clause. Please see attached accdb and xls file. I have the following but it just returns the grand...
access
running sum
  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    Nov 14, 2024

    Hi,

    The criteria concatenation in

    DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] And [ID]<=[ID])) AS RunningSum

    should be

    DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And ID<=" & [ID])) AS RunningSum

     

    Servus
    Karl
    ****************
    Access Forever News DevCon
    Access-Entwickler-Konferenz AEK

arnel_gp's avatar
arnel_gp
Steel Contributor
Nov 14, 2024

you can't use DSum() on your RunningSum because:

  1. there are ID that have big ID yet earlier date (maybe late entry?)
  2. there are dates that fall on the same date (facIDfk = 3 , sep-26-2023, 3 records).

 

so your last hope is the function i provided.

Tony2021's avatar
Tony2021
Steel Contributor
Nov 14, 2024

Hi Arnel,

Yes, you are correct with your points above.  

I dont think #2 is as concerning though since I think <= on [DateRecd] would capture those records where they are on the same dates.  

Could the function be revised to base it on the [DateRecd] instead of the [ID]?  If too time consuming I can use the Dsum since it does work.  

RunningSum: DSum("Amount","tblDrawsDetails","FacIDfk=" & [FacIDfk] & " And nz([DateRecd],0) <=" & Format$(Nz([DateRecd],0),"\#mm\/dd\/yyyy\#"))

thank you sir!