Hi Experts,

I have been fiddling around with this query for quite some time.  I am getting nowhere with it.  What I have resorted to is trying to use MonthYearSort in a Where clause in the following OpeningBalance calculation (I typically would use an ID but I dont have one.  I dont know if this is a good idea…probably not however it is unique but just not a typical ID since its based on a date).  Not sure if some special kind of formatting would need to be used since it is based on a date.  I am grabbing a sum from another query and summing by month based on the MonthYearSort.

OpeningBal: format(Nz(DSum(“SumOfAmount”,”qryDrawsDecliningCumDrawn”,”Type=” & [tblDraws].[Type] & ” And [MonthYearSort] < ” & Nz([qryDrawsDecliningCumDrawn].[MonthYearSort],0)),0),”Currency”)

MonthYrSort: Year([FundingDate])*12+DatePart(‘m’,[FundingDate])-1

thank you. 

​ Hi Experts,I have been fiddling around with this query for quite some time.  I am getting nowhere with it.  What I have resorted to is trying to use MonthYearSort in a Where clause in the following OpeningBalance calculation (I typically would use an ID but I dont have one.  I dont know if this is a good idea…probably not however it is unique but just not a typical ID since its based on a date).  Not sure if some special kind of formatting would need to be used since it is based on a date.  I am grabbing a sum from another query and summing by month based on the MonthYearSort. OpeningBal: format(Nz(DSum(“SumOfAmount”,”qryDrawsDecliningCumDrawn”,”Type=” & [tblDraws].[Type] & ” And [MonthYearSort] < ” & Nz([qryDrawsDecliningCumDrawn].[MonthYearSort],0)),0),”Currency”)MonthYrSort: Year([FundingDate])*12+DatePart(‘m’,[FundingDate])-1  thank you.   Read More

​