Possibly Iif statement? Can you help?

[aellistechsupport]

If the May 2012 memberships ends in 2015, as shown in the table, those would ONLY show in the 1st column Jul NOT in the May and Jun column they are currently showing.

See 1 under May and Jun columns: Since they end in 2015 they should be counted with the Jul column and NOT show in the May and Jun column of the fiscal year.

Fiscal year = Jul - Jun
Where the payments for memberships are collected starting in May and Jun for the future Season and these payments need to be counted with the purchases through Jul as the Jul total (May-Jul). These same purchases should NOT be shown again in the last two months of the fiscal year in the columns May and Jun since the actual payments for these months (are for seasons ending later than 2015).

Season	YearPaid	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	PaymentDate	EndDate	MembershipNumber
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/15/2012	6/30/2015	1144627
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/16/2012	6/30/2015	1005705
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/16/2012	6/30/2015	1099838
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	6/28/2012	6/30/2015	1131963
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	7/19/2012	6/30/2015	1391914

Please see: Collegiates.zip



Example:

Apr: 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)))

What edit in this formula to handle if 5 and 6 have the SAME Season as shown to NOT include them?
Or another formula on top of this one?

The 5 records with May-2012 AND May-2013 all have EndDates of Jun-2015 (Season ending 2015), these should only show in the 1st column of the report in the month of Jul BUT NOT again in the last months of the fiscal year in Apr, May, Jun columns.

So, how do I edit the formula or add a formula to say something like exclude May and Jun IF the Season ending year is the SAME as the ending year it's currently reporting on? Seems like that will work but not sure how to write that?

Let's say one of these bolded May payment end has a Season greater than 2015, THEN that should NOT be included into the Jul month and should show in the May column at the end of the monthly cumulative report.

MemberType	Season	MembershipNumber	PaymentDate	EndDate
Collegiate	2015	1144627	15-May-12	30-Jun-15
Collegiate	2015	1005705	16-May-12	30-Jun-15
Collegiate	2015	1099838	16-May-12	30-Jun-15
Collegiate	2015	6436112	07-May-13	30-Jun-15
Collegiate	2015	1252333	28-May-13	30-Jun-15
Collegiate	2016	6302859	01-May-13	30-Jun-16
Collegiate	2016	8919316	01-May-13	30-Jun-16
Collegiate	2017	1060155	02-May-14	30-Jun-17
Collegiate	2017	8746552	03-May-14	30-Jun-17
Collegiate	2017	1068751	07-May-14	30-Jun-17
Collegiate	2017	1276092	09-May-14	30-Jun-17
Collegiate	2017	6512612	15-May-14	30-Jun-17
Collegiate	2017	1530334	25-May-14	30-Jun-17

In other words ...

How can this be edited

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)))

to then say something like WHEN the Season or EndDate is the same THEN exclude it in this formula?

[June7]

Maybe:

IIf(Year([Season])=Year([EndDate]), Null, 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))))

[aellistechsupport]

Think you're on the right path ...

problem though, it came up completely NULL. It should have only excluded 5 from the 2015 row, etc ... totaling 13 excluded for all for Seasons. the 13 shown in the table above

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	EndDate
Collegiate	2015	549	660	731	814	861	889	917	935	949		954	954	6/30/2015
Collegiate	2016	520	617	716	795	822	836	856	874	887		889	889	6/30/2016
Collegiate	2017	537	622	693	813	855	869	901	921	931		941	941	6/30/2017
Collegiate	2018	628	721	826	888	936	972	1020	1042	1050		1055	1055	6/30/2018

perhaps something like this?
But that it shouldn't be Null, it should be the cumulative total MINUS the May and Jun with the same Season year as those purchases were already included in the 1st column Jul.

[June7]

Sorry, should have looked at expression more closely and done testing.

I don't really understand the expression. What does And Not 7=0 accomplish?

Maybe:

Jun: Sum(IIf([Season]=Year([EndDate]),Null,IIf(Month([PaymentDate])>=7 Or Month([PaymentDate])<=6,1,0)))

[aellistechsupport]

Your last example also results in Null (blank values) in the Jun column.

[aellistechsupport]

Scrap all that and look it another way?

How do you write this in proper format?
WHERE (((Month([PaymentDate]))<>Month(Date())))
EXCEPT when the current month is 5 or 6?

I want to say exclude the current month's data except if the current month is May or Jun.

I think this will solve it.
I've tried the In and Not In and it doesn't work right.

[June7]

Maybe:

Month([PaymentDate])<>Month(Date()) AND Month(Date()) <> 5 AND Month(Date()) <> 6

[aellistechsupport]

It resulted in Null?

I tried this, though it results in the grand total of 3839 when I tried to test it for the prior month I get the same instead of 3817.

SELECT Count([02_Collegiate_Details].MembershipNumber) AS CountOfMembershipNumber
FROM 02_Collegiate_Details
WHERE (((Month([PaymentDate]))<>Month(Date())-1 Or Month(Date())<>5 Or Month(Date())<>6));

I tried this and I do get 3817 BUT then I only get 3826 with this instead of 3839

<>Month(Date())-1 Or (Month(Date())<>5 And Month(Date())<>6)

Unless I'm forgoing something I'm not realizing?

[June7]

Sorry, I am lost.

Explicitly describe the rules you want to build expression for.

[aellistechsupport]

ok, sorry for any confusion ...

Ok, so with this formula:

SELECT Count([02_Collegiate_Details].MembershipNumber) AS CountOfMembershipNumber
FROM 02_Collegiate_Details
WHERE (((Month([PaymentDate]))<>Month(Date())-1));

I get this:

CountOfMembershipNumber
3817

That was to show you that when ran running last month it results in the correct total of 3817.
The code being at the time

WHERE (((Month([PaymentDate]))<>Month(Date())-1));

Now when it's ran this month of May it shows only 3826. It should be 3839. The difference between when ran last month of 3817 and this month of what it should be, 3839, is 22 records.

CountOfMembershipNumber
3826

These are the 22 records in Apr:

Season	Total Of EndDate	Apr	MembershipNumber	PaymentDate	EndDate
2015	1	1	942501	4/1/2012	6/30/2015
2015	1	1	1395682	4/6/2012	6/30/2015
2015	1	1	1486428	4/8/2012	6/30/2015
2015	1	1	6007168	4/17/2012	6/30/2015
2015	1	1	8762208	4/27/2012	6/30/2015
2016	1	1	924553	4/30/2013	6/30/2016
2016	1	1	1040804	4/20/2013	6/30/2016
2017	1	1	1063631	4/23/2014	6/30/2017
2017	1	1	1063633	4/23/2014	6/30/2017
2017	1	1	1063707	4/23/2014	6/30/2017
2017	1	1	1063708	4/23/2014	6/30/2017
2017	1	1	1126299	4/13/2014	6/30/2017
2017	1	1	1438341	4/11/2014	6/30/2017
2017	1	1	1482073	4/3/2014	6/30/2017
2017	1	1	1537156	4/12/2014	6/30/2017
2017	1	1	6303630	4/3/2014	6/30/2017
2017	1	1	6323641	4/14/2014	6/30/2017
2018	1	1	1191344	4/16/2015	6/30/2018
2018	1	1	1314460	4/29/2015	6/30/2018
2018	1	1	1497703	4/2/2015	6/30/2018
2018	1	1	1518957	4/7/2015	6/30/2018
2018	1	1	6519193	4/14/2015	6/30/2018

Total here is: 3817 where the Season 2018 shows 1050 last month excludes these:

2018	1	1	1191344	4/16/2015	6/30/2018
2018	1	1	1314460	4/29/2015	6/30/2018
2018	1	1	1497703	4/2/2015	6/30/2018
2018	1	1	1518957	4/7/2015	6/30/2018
2018	1	1	6519193	4/14/2015	6/30/2018

Making it:

Season	Total Of EndDate
2015	949
2016	887
2017	931
2018	1050

These 13 May records need to show but it is NOT because of the simple statement of <>Month(Date()), which is true in itself BUT I need it to show them ONLY when the month is in May or Jun.

Season	Total Of EndDate	May	MembershipNumber	PaymentDate	EndDate
2015	1	1	1005705	5/16/2012	6/30/2015
2015	1	1	1099838	5/16/2012	6/30/2015
2015	1	1	1144627	5/15/2012	6/30/2015
2015	1	1	1252333	5/28/2013	6/30/2015
2015	1	1	6436112	5/7/2013	6/30/2015
2016	1	1	6302859	5/1/2013	6/30/2016
2016	1	1	8919316	5/1/2013	6/30/2016
2017	1	1	1060155	5/2/2014	6/30/2017
2017	1	1	1068751	5/7/2014	6/30/2017
2017	1	1	1276092	5/9/2014	6/30/2017
2017	1	1	1530334	5/25/2014	6/30/2017
2017	1	1	6512612	5/15/2014	6/30/2017
2017	1	1	8746552	5/3/2014	6/30/2017

So the 5 in red from Apr would now show when ran this month and totaling 3839

Season	Total Of EndDate
2015	954
2016	889
2017	941
2018	1055

HOWEVER with the <>Month(Date()) it's excluding the May and it'll exclude Jun when ran next month.
So right now, with this formula it shows only 3826 records. The 13 May records shown above.

I am using the database and queries attached here: Collegiates.zip

[aellistechsupport]

Every month the report is ran it needs to exclude the current month data since the current month data won't be complete until the month is over.
SO in Apr, those 5 records from Apr 2015 purchases won't show until this month ran the report is ran.

It works up to Apr BUT when running it in this month of May (it'll be the same for Jun).
the <>Month(Date()) doesn't work because it's excluding those 13 May records. BUT they still need to show because they are counted at the beginning of the fiscal year into the Jul column.

Season			MembershipNumber	PaymentDate	EndDate
2015			1005705	5/16/2012	6/30/2015
2015			1099838	5/16/2012	6/30/2015
2015			1144627	5/15/2012	6/30/2015
2015			1252333	5/28/2013	6/30/2015
2015			6436112	5/7/2013	6/30/2015
2016			6302859	5/1/2013	6/30/2016
2016			8919316	5/1/2013	6/30/2016
2017			1060155	5/2/2014	6/30/2017
2017			1068751	5/7/2014	6/30/2017
2017			1276092	5/9/2014	6/30/2017
2017			1530334	5/25/2014	6/30/2017
2017			6512612	5/15/2014	6/30/2017
2017			8746552	5/3/2014	6/30/2017

Since May and Jun payments are received for the next Season and these + Jul total needs to show in the 1st column of Jul.
HOWEVER they can't get excluded when running it this month with the <>Month(Date()) because of what I just stated.

I think I need some sort of exclusion / exception while using

     <>Month(Date())

since this works until the month of May and Jun comes around.
BUT when they do, like we are now, it can't exclude current month which is what the criteria says.

[aellistechsupport]

I also tried this, but same thing as your suggestions, results in Null

IIf(Year([PaymentDate]<Year(Date())),Month(Date()),<>Month(Date()))

Collegiates.zip

[aellistechsupport]

Does anyone know how to INCLUDE the month of May or June data when the current month is May and if the current month is June but all other months exclude the "current" month?

I'm working with

  <>Month(Date())

to exclude current month, however I need ALL months when the current month is May or current month is June. Meaning only in the month of May or month of June I need the <>Month(Date()) to NOT take affect.

<>Month(Date()) and (Month(Date()) Not In (5,6))

does not work, it results in Null

<>Month(Date()) and (Month(Date()) In (5,6))

does not work, it also results in Null

There has to be a way to do this, anyone know the correct formula?

[aellistechsupport]

See attached,
I need to have the numbers match from Query1 and ByFiscalYear when reporting in the month.

It works for

 <>Month(Date()) or 5 or 6

then checking it for if ran last month as the current reporting month with

<>MonthDate())-1 or 5 or 6

they match.

However when I try to test couple months prior

 <>Month(Date())-2  or 5 or 6

and yet another month prior to that

  <>Month(Date())-3 or 5 or 6

they do not match.

Please see attached Collegiates.zip

[aellistechsupport]

I'm further now then before but stuck in verifying past Dec.
Any idea?

IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=Month(Date())-6,1,0)))

If I do -7 or -8, etc ... to take me back to last Jul, I get the same total as I did for Dec at 3566. Not sure why?