Forum Discussion
Sum between 2 dates
Hello Experts, I need to sum [AmountOrig] but only for a specified date range. Please see bold below. Its not correct since it returns NULL. I am not entirely certain if Dsum is what I need though since tblFXparent is in the query itself but if I try "sum" I get an aggregate function error (or something like that).
thank you.
SELECT tblFXParent.*, [rate]*[AmountOrig] AS USD, Nz([AmountOrig],0)+Nz(DSum("AmountRoll","tblFXRollsChild","[IDParentfk]=" & [IDFXParent]),0) AS [Balance EURO], tblCompanies.CompanyName, IIf(IsNull([DateSettleC]),[DateSettle],[DateSettleC]) AS DtSettNew, DSum("AmountOrig","tblFXparent","DateSettle = " Between #5/20/2021# And #9/15/2021#) AS FCNotionals
FROM tblFXParent INNER JOIN tblCompanies ON tblFXParent.Vendor = tblCompanies.CoID
ORDER BY tblFXParent.Vendor, tblFXParent.DateSettle;
- your Criteria on Dsum() should be one expression:
DSum("AmountOrig","tblFXparent","Vendor = '" & [Vendor] & "' And DateSettle Between #5/20/2021# And #9/15/2021#") AS FCNotionals
or you can create a separated Aggregate query and join it with your first query:
select Vendor, Sum(AmountOrig) As FCNotionals From tblFXParent where DateSettle Between #5/20/2021# And #9/15/2021# Group by Vendor
- your Criteria on Dsum() should be one expression:
DSum("AmountOrig","tblFXparent","Vendor = '" & [Vendor] & "' And DateSettle Between #5/20/2021# And #9/15/2021#") AS FCNotionals
or you can create a separated Aggregate query and join it with your first query:
select Vendor, Sum(AmountOrig) As FCNotionals From tblFXParent where DateSettle Between #5/20/2021# And #9/15/2021# Group by Vendor- I see where I am wrong. thanks again for the expert help! Really appreciate it.
