Syntax? Sum on Iif statement based on date?

[June7]

The expressions appear to just be a count summation, not summing a field, and more complex than necessary.

SELECT MemberGroup, MemberType, Sum(IIf(Month([paymentdate])=7,1,0)) AS Jul, ...
FROM dbo_v030mbrshp01PdMembers
WHERE ...



The trick, as you are finding, is getting the filter criteria correct. I recently had to deal with this issue of retrieving records by fiscal year. I calculate a field in query to assign each record a fiscal year value. Then I select records by filter criteria on this FY field.

FY: Year([paymentdate])+IIf(Month([paymentdate])>6,1,0)

Could include that FY calc in the GROUP BY clause.

I don't like to use dynamic parameters in query. I apply dynamic filter when report or form is opened by reference to control on form:
DoCmd.OpenReport "report name", , , "FY=" & Me.tbxFY

Also, instead of doing aggregate calcs in query, consider a report using Grouping & Sorting functionality with aggregate calcs in group header/footer sections.

[aellistechsupport]

Hi,
now that the fiscal year has changed, I'm seeing ... issues.

was wondering if you could help?

Issue is for a set of records where:
1. Payment date goes back 3 years
2. Membership end date goes forward 4 years

So this data set needs to produce:

Group - SeasonYearEnd Jul Aug, etc ... Jun where:

ABCD - 2015 (current yr) Jul to consist of Prior fiscal year May+Jun+Jul, cumulating w/ Aug for Aug, etc ... until the last fiscal month June.
ABCD - 2016 (current yr+1) Jul to consist of Prior fiscal year May+Jun+Jul, cumulating w/ Aug for Aug, etc ... until the last fiscal month June.
ABCD - 2017 (current yr+2) Jul to consist of Prior fiscal year May+Jun+Jul, cumulating w/ Aug for Aug, etc ... until the last fiscal month June.
ABCD - 2018 (current yr+1) Jul to consist of Prior fiscal year May+Jun+Jul, cumulating w/ Aug for Aug, etc ... until the last fiscal month June.

The current code, I believe, does not know how to handle the repeating months of May and June from the prior year to current year May and June.

Here is the first column Jul (should be May+Jun+Jul of prior fiscal year) Fiscal year is Jul-Jun. Where payments received starting May through Jun is for the next fiscal year. Why they need to show the numbers from May and Jun to add with Jul for the Jul bucket.

First month of the Fiscal Year is Jul.

Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])=5 Or DatePart("m",[PaymentDate])=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0)))

Last month of the fiscal year is Jun:

 Jun: IIf(Sum(IIf(DatePart("m",[PaymentDate])=5 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=7 ,1,0)))

WHERE: PaymentDate:
Between IIf(Month(Date())<=5,DateSerial(Year(Date()),5,1),DateSerial(Year(Date())+4,5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date())+4,6,30),DateSerial(Year(Date())+4,6,30)

[June7]

Don't really understand what you want but sounds like you might need to calculate another field for a quarter value that can be used for grouping criteria.

Qtr: DatePart("q",[datefield)

or

FQtr: Choose(Month([datefield]), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)

That expression will return 1 for months Jan/Feb/Mar, 2 for Apr/May/Jun, 3 for Jul/Aug/Sep, 4 for Oct, Nov, Dec. If you want 1 for Jul/Aug/Sept, then adjust the numbers in the expression.

[aellistechsupport]

Don't really understand what you want but sounds like you might need to calculate another field for a quarter value that can be used for grouping criteria.

FQtr: Choose(Month([datefield]), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)

That expression will return 1 for months Jan/Feb/Mar, 2 for Apr/May/Jun, 3 for Jul/Aug/Sep, 4 for Oct, Nov, Dec. If you want 1 for Jul/Aug/Sept, then adjust the numbers in the expression.

Sorry, no I don't believe that will work.
It's cumulative months for the fiscal year. where the fiscal year is July - June. So now that it's August the current fiscal year is July 2014 through June 2015.

WHERE the month of July 2014 should include invoices from May and June AND July 2014 for the Jul column.
And the Aug column should be May-Aug and so on ending with the last month column of Jun which is May 2014 - June 2015.

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear;

However this is resulting this:

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	553	664	735	818	860	888	916	934	948	953	958	958
Collegiate	2016	523	620	721	800	825	839	859	877	890	892	894	894
Collegiate	2017	539	624	695	816	852	866	898	918	928	938	944	944
Collegiate	2018		648	648	648	648	648	648					648

the last row with the Season ending in 2018, the 648 in Jun should show in the first column July since July should be May-Jul 2014 invoices.
The details show it's 642 invoices from May-July 2014:


And so far for August 2014 it's 12 records.
So Jul column should be May-Jul 2014 of 642 showing
then Aug column 642 + 12 = 654

as you can see in the first summary shows Null in the Jul column and 648 in Aug?

MemberType	Season	EndDate	PaymentDate
Collegiate	2018	6/30/2018	8/2/2014
Collegiate	2018	6/30/2018	8/2/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014

[June7]

Okay, that sounds like a running sum. Running sum is not easy on form or query. Common topic. Here is one recent: https://www.accessforums.net/queries...ery-45841.html

[aellistechsupport]

Anyone out there take a look at the code and let me know how to fix it to handle the years for the same months?
May and June. May 2014, Jun 2014, May 2015, Jun 2014


The code is working, I think EXCEPT which year it needs to total for May and June since May and June totals twice.
In that I think it’s confused on what year the May and June totals are.
If you look at the 02_Collegiate query I’m not sure how it knows which year to get the figures for.
It is cumulating and the correct months but not sure if it’s cumulating the correct year for the month.

1) Fiscal year is July – June
a. So right now fiscal year is officially July 2014 – June 2015
b. But needs to show cumulative totals from May 2014 and total it each month until the end of June 2015.
i. Because payments for future season (enddate field) are received starting May and they need to show those invoice counts in the next fiscal year although the payments are received in the current fiscal year or now that it’s August, last fiscal year.
ii. That said you can see how it has to deal with May 2014 and June 2014 AND May 2015 and June 2015 when those months and year come.
iii. May 2014, Jun 2014, Jul 2014 into Jul column
iv. May 2014-Aug 2014 in Aug column
v. May 2014-Sep 2014 in Sep column, etc … until
vi. May 2014-May 2015 in May column <-- this is where I need to verify the code below is correct on handling the years for the month of May
vii. May 2014-Jun 2015 in Jun column <-- this is where I need to verify the code below is correct on handling the years for the month of May

So when looking at the first month Jul column: The code needs to total May and Jun and Jul of last FISCAL year if it’s < then July 2014, if it’s July 2014 or greater it should cumulate the total of this year May and June.

IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul

And the last two columns May and Jun: The code needs to total May – May (1 year span, last May through this May which is May 2014 – May 2015 if it’s July this year or greater.

IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun

With the payment date parameter of:

((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))

NOTE: the EndDate (Season) for the Collegiate is 4 year memberships, so it would show, now that it's August and in the new fiscal year, 2015-2018 for the season column as can be seen in the example.
And payments are each year.

So come next July (July 2015) it would flip into the next fiscal year and show seasons 2016-2019.


I’m not sure if it’s correct because, if you look at Season ending in 2018 (Year([EndDate])), it has Null value in the 1^st month Jul (which is 2014 but having the totals from May 2014-Jul-2014) and a value in the last month Jun (which is for Jun 2015 and would be totals from May 2014-June 2015)

02_Collegiate
MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	553	664	735	818	860	888	916	934	948	953	958	958
Collegiate	2016	523	620	721	800	825	839	859	877	890	892	894	894
Collegiate	2017	539	624	695	816	852	866	898	918	928	938	944	944
Collegiate	2018		648	648	648	648	648	648					648

[aellistechsupport]

Viola!
i think i got it.

anyone see a problem, please speak up.

anyone looking for something like this, check it out

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear;