Criteria with IIFs?

[aellistechsupport]

I have a criteria on EndDate so I only see EndDates from last season and this season.


Where Season is from July - June

((dbo_v030mbrshp01PdMembers.EndDate)=IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)))

I am trying to figure out how to incorporate that when I am doing this translation, if you will

DateEnd: IIf([EndDate]<>DateSerial(Year(Date()),6,30),DateSerial(Year(Date()),6,30),[EndDate])

Not sure if I'm making sense ...

Trying to change the EndDate if it already isn't 6/30/yy, depending on the season it is.
Guess the question really is, how do I write if EndDate isn't already 6/30/yy (since last year), then change it to 6/30/yy greater than the existing StartDate.

[June7]

So if the StartDate is Jul - Dec, use the current year, otherwise add 1 to current year? Or the reverse? I would add 1 if month falls in Jul - Dec to return fiscal year identifier.

[aellistechsupport]

Season is July - June
You can start your membership at pretty much anytime, but some start paying as early as May for the next season.
Seasons end 6/30/2016 and for FOFS memberships, these end 9/30/2016.

These are the season end dates in any year: 6/30/yy and 9/30/yy

DateEnd is the derived field

MembershipNumber	PaymentDate	DateEnd	EndDate	StartDate
584281	7/15/2015	6/30/2016	12/4/2015	7/15/2015
1239376	10/12/2015	6/30/2016	1/12/2016	10/12/2015

Reason why there are these EndDates outside of the actual season end dates is because their membership ended for reasons such as:
1. Cancelled their membership
2. Transferred their membership

But because Finance wants to see the memberships that were paid, in the month they were paid for the season, they don't care to see that they ended and in order to determine "current" season, the use of 6/30/yy and 9/30/yy are necessary with a PaymentDate parameter.

[June7]

I am way more confused. You have two overlapping seasons?

Yes, I know DateEnd is the calculated field - I am just not understanding the rules for the calculation.

[aellistechsupport]

data set is historical and there's another report to compare seasons (prior and current)

trying to determine the rule but what i wrote, I think
IF the startdate is greater than 7/1/yy and the enddate is greater than startdate but not equal to 6/30/yy or 9/30/yy then enddate should show 6/30/yy that's greater than the startdate when the MemberTypeID <> between 24 and 31 and 9/30/yy if MemberTypeID is between 24 and 31

[June7]

Ouch!

That would be a complicated nested IIf() expression. This is screaming for a VBA custom function. Something like:

Function GetDateEnd(dteStart As Date, dteEnd As Date, intType As Integer) As Date
If dteStart > DateSerial(Year(Date), 7, 1) And dteEnd > dteStart And dteEnd <> DateSerial(Year(Date), 6, 30) And dteEnd <> DateSerial(Year(Date), 9, 30) Then
    If intType > 23 And intType < 31 Then
        GetDateEnd = DateSerial(Year(Date) + IIf(Month(dteStart) > 9, 1, 0), 9, 30)
    Else
        GetDateEnd = DateSerial(Year(Date) + IIf(Month(dteStart) > 6, 1, 0), 6, 30)
    End If
Else
    'what should go here?
End If
End Function

Adjust the logic as you see fit. Call the function from query or textbox on form or report.

[aellistechsupport]

Oie!
Ok, lets simply it ...

how about this ...

How do you say
IF EndDate > StartDate AND <> 6/30/yyyy then IF EndDate <> 6/30/yyyy then EndDate 6/30/yyyy else EndDate
Taking into account the IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30) ,DateSerial(Year(Date())+1,6,30) since the season is July-June.

So 6/30/2015 and 6/30/2016 into consideration.
Come 7/1/2016, it'll consider 6/30/2016 and 6/30/2017

Query:

SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, "Mth" & DateDiff("m",[PaymentDate],IIf(Month(Date())<=7,DateSerial(Year(Date())-1,6,30),DateSerial(Year(Date()),6,30))) AS FiscalMonthReporting, dbo_v030mbrshp01PdMembers.MembershipNumber, dbo_v030mbrshp01PdMembers.StartDate, dbo_v030mbrshp01PdMembers.PaymentDate, dbo_v030mbrshp01PdMembers.EndDate, IIf([EndDate]<>DateSerial(Year(Date()),6,30),DateSerial(Year(Date()),6,30),[EndDate]) AS DateEnd, dbo_v030mbrshp01PdMembers.InvoiceNumber
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.MemberGroup) Like "Basic*") AND ((dbo_v030mbrshp01PdMembers.StartDate)<=[EndDate]) AND ((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))))
ORDER BY dbo_v030mbrshp01PdMembers.StartDate, dbo_v030mbrshp01PdMembers.PaymentDate, dbo_v030mbrshp01PdMembers.EndDate;

Result sampling:
The DateEnd is not correct. it should be 6/30/2015 for these records.

MemberTypeID	MemberGroup	MemberType	FiscalMonthReporting	MembershipNumber	StartDate	PaymentDate	EndDate	DateEnd	InvoiceNumber
9	Basic Skills Member	New Basic Skills Member	Mth1	1201730	4/30/2015	5/1/2015	6/30/2015	6/30/2016	0043191
9	Basic Skills Member	New Basic Skills Member	Mth1	1200325	5/1/2015	5/1/2015	5/1/2015	6/30/2016	0043206
9	Basic Skills Member	New Basic Skills Member	Mth1	1201834	5/1/2015	5/1/2015	5/6/2015	6/30/2016
41	Basic Skills Member	Renewing Basic Skills Member	Mth1	1202097	5/1/2015	5/1/2015	5/31/2015	6/30/2016	43206
9	Basic Skills Member	New Basic Skills Member	Mth1	1200359	5/1/2015	5/1/2015	6/14/2015	6/30/2016	43206

It's correct here however:

MemberTypeID	MemberGroup	MemberType	FiscalMonthReporting	MembershipNumber	StartDate	PaymentDate	EndDate	DateEnd	InvoiceNumber
44	Basic Skills Member	Renewing Basic Skills Instructor - No Program	Mth0	1555102	7/1/2015	6/4/2015	7/15/2015	6/30/2016	0045090
41	Basic Skills Member	Renewing Basic Skills Member	Mth0	1212365	7/1/2015	6/5/2015	10/21/2015	6/30/2016	0050791
41	Basic Skills Member	Renewing Basic Skills Member	Mth0	1212352	7/1/2015	6/5/2015	10/21/2015	6/30/2016	0050791
9	Basic Skills Member	New Basic Skills Member	Mth0	1212366	7/1/2015	6/5/2015	11/22/2015	6/30/2016	0050791
41	Basic Skills Member	Renewing Basic Skills Member	Mth0	1083226	7/1/2015	6/5/2015	12/21/2015	6/30/2016	0050791

[June7]

Maybe the current date has nothing to do with determining what season period a record falls into. Maybe it's the year of the PaymentDate or EndDate.

I am still not understanding the rules for this calc.

[aellistechsupport]

it takes both into consideration both.
however it is ultimately the EndDate that tells you the season the payment record falls into.

1. You purchase a membership for a season.
2. Say you paid this month then your membership is for the current season ending 6/30/2016.
3. IF say you renewed or a new membership this May, you could pay for the current season ending 6/30/2016 BUT being so close to the end of the current season you want it for the next season, 6/30/2017.
4. You can start a membership at any time.
5. You know that seasons end on 6/30/yyyy and that a season is from July 1st - June 30th (12 months)
6. StartDate is when you initiate your membership, sometimes it matches the PaymentDate but not always
7. You could cancel your membership at anytime
8. A membership may have been applied to the wrong record
9. Either case the EndDate changes on the original invoice with an actual season EndDate to the "End date" of when your membership ended, for other reasons then the season actually ends.
10. Finance wants to see, not from the Great Plains and in the AR or GL but from the membership database, in order to try and reconcile that the records are being recorded properly, what memberships are booked each month (payment date) for the current season (enddate)
11. So it doesn't matter to them, for this particular report, that the original membership ended outside of the actual season ending because it was canceled or returned. They just want to see the payment (PaymentDate) was received in what month for the current season (EndDate).
12. PaymentDate drives the monthly columns of the total number of membership records that were generated in the season (12 month period).
13. EndDate drives which season the membership ends EXCEPT when it ends for other reasons such as cancellations or returns, etc ...
14. BUT because the EndDate of the original record gets updated to show that the membership ended before the season end date.
15. Since it's dynamically updating the report each month, it looks at that record where the EndDate changed and drops that count. They don't want it to drop. The payment was received in that month, nothing else matters (meaning even though it was returned or cancelled), they still want to see that counted in the payment month.
16. They then want to see a comparison from prior season to current season.

That said,
How do you write / code:
IF EndDate > StartDate AND <> 6/30/yyyy then IF EndDate <> 6/30/yyyy then EndDate 6/30/yyyy else EndDate
Taking into account the IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30) ,DateSerial(Year(Date())+1,6,30) since the season is July-June.

Take 6/30/2015 and 6/30/2016 into consideration for the comparison.
Come 7/1/2016, it'll consider 6/30/2016 and 6/30/2017

[June7]

Then build that into the logic of the VBA function.

[aellistechsupport]

There lies the problem, not sure how exactly ...

[aellistechsupport]

I am trying to do something like this in a query:

IIF([StartDate]<=[EndDate], IIF([PaymentDate] Is Between IIf(Month(Date())<=7,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)), IIF(EndDate]<>DateSerial(Year(Date()),6,30) And > DateSerial(Year(Date())-1,6,30)  , DateSerial(Year(Date()),6,30), [EndDate]))

The current full query is

SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, "Mth" & DateDiff("m",[PaymentDate],IIf(Month(Date())<=7,DateSerial(Year(Date())-1,6,30),DateSerial(Year(Date()),6,30))) AS FiscalMonthReporting, dbo_v030mbrshp01PdMembers.MembershipNumber, dbo_v030mbrshp01PdMembers.PaymentDate, dbo_v030mbrshp01PdMembers.StartDate, dbo_v030mbrshp01PdMembers.EndDate, IIf([EndDate]<>DateSerial(Year(Date()),6,30),DateSerial(Year(Date()),6,30),[EndDate]) AS DateEnd, dbo_v030mbrshp01PdMembers.InvoiceNumber
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))) AND ((dbo_v030mbrshp01PdMembers.StartDate)<=[EndDate]) AND ((dbo_v030mbrshp01PdMembers.EndDate)<>#6/30/2016# And (dbo_v030mbrshp01PdMembers.EndDate)>#6/30/2015#))
ORDER BY dbo_v030mbrshp01PdMembers.PaymentDate, dbo_v030mbrshp01PdMembers.StartDate, dbo_v030mbrshp01PdMembers.EndDate;

But I want the IIF to be in the DateEnd derived field since I need it to derive only when those conditions meet BUT still need to see the rest of the records when the records do not meet those conditions.

Right now the query limits to the conditions then and then derives the results as DateEnd.

[June7]

If you want to see all records then why is there even a WHERE clause?

[aellistechsupport]

If you want to see all records then why is there even a WHERE clause?

That is what I was saying, I want it to show all of it but derive where it meets those conditions in the DateEnd field only.
As mentioned, I can only figure out how to do the WHERE to show the ones I want derived BUT need to change the nested IIF to do the WHERE in the IIF only as DateEnd.

Did that make better sense?

As posted, I am trying to write something like that in DateEnd field with the nested IIF where the query is the result of it BUT it is ONLY when the conditions meet. I need it changed so it's doing ALL of that in the DateEnd nested if so I can see ALL of the records and have the DateEnd show the results of the IIF

[aellistechsupport]

I want to say
IF the StartDate is greater or equal to 7/1/yy or between 7/1/yy and 6/30/yy of last year AND EndDate is <> 6/30/yy of last year THEN 6/30/yy of last year BUT
IF the StartDate is greater or equal to 7/1/yy of or between 7/1/yy and 6/30/yy this year AND EndDate is <> 6/30/yy of this year THEN 6/30/yy of this year

This isn't correct, but working with this:

DateEnd: IIf([StartDate]>=DateSerial(Year(Date())-1,7,1),DateSerial(Year(Date())-1,6,30),IIf([StartDate]>=DateSerial(Year(Date()),7,1),DateSerial(Year(Date()),6,30),[EndDate]))