Nested IIFs?

[aellistechsupport]

I am trying to write:
IF the StartDate is 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 as DateEnd BUT
IF the StartDate is between 7/1/yy and 6/30/yy of this year AND EndDate is <> 6/30/yy of this year THEN 6/30/yy of this year as DateEnd

This isn't correct, but working with this:

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

[CJ_London]

explain in simple English what you are trying to do as it is not clear from your code - use some examples with real dates

You seem to be wanting to determine a dateend, but you have that in your data

IF the StartDate is greater or equal to 7/1/yy - what would yy be for example? this year? last year?

or between 7/1/yy and 6/30/yy of last year - and again what would yy be?

etc

[aellistechsupport]

The existing EndDate gets updated when a membership is ended because of a cancellation or something else besides the actual season ending. Which is always on 6/30/yy.

I am trying to get a derived EndDate in a new field named DateEnd to produce them to show a 6/30/yy based on the logic, which encompasses the following:
NOTING that the season is July - June.

1. If the StartDate is between 7/1/14 and 6/30/15 of last year and the EndDate is not 6/30/15 then the DateEnd field should produce 6/30/2015 otherwise just show the EndDate with 6/30/15.
2. If the StartDate is between 7/1/15 and 6/30/16 of this year and the EndDate is not 6/30/16 then the DateEnd field should produce 6/30/2016 otherwise just show the EndDate with 6/30/16.

Need the two years for a comparison. That is why the "code" needs to handle the two years.

Examples:

StartDate	EndDate	DateEnd
06/01/15	07/01/15	06/03/15	06/30/16
06/05/15	07/01/15	06/11/15	06/30/16
06/09/15	07/01/15	06/13/15	06/30/16
06/11/15	07/01/15	06/15/15	06/30/16
06/16/15	07/01/15	06/15/15	06/30/16
06/16/15	07/01/15	06/16/15	06/30/16
06/17/15	07/01/15	06/16/15	06/30/16
06/17/15	07/01/15	06/16/15	06/30/16

StartDate	EndDate	DateEnd	InvoiceNumber
5/1/2015	4/28/2015	4/28/2015	43206
5/1/2015	4/29/2015	4/29/2015	43206
5/1/2015	5/1/2015	5/1/2015	0043206
5/1/2015	5/6/2015	5/6/2015
5/1/2015	5/31/2015	5/31/2015	43206
5/1/2015	6/14/2015	6/14/2015	43206

[CJ_London]

still doesn't make much sense to me

you say

the EndDate is not 6/30/15 then the DateEnd field should produce 6/30/2015 otherwise just show the EndDate with 6/30/15.

- so if enddate equals 06/30/2015, make it 06/30/2015 otherwise make it 06/30/2015 = the same value?

and in the first row of you example

startdate is between 7/1/14 and 6/30/15 but end date is 07/01/15 - so it should be producing a result of 6/30/15, but you are showing 06/03/15 - and in the next row, same applies but you are showing 06/11/15

[aellistechsupport]

Oops, that was supposed to say
IF the EndDate has a 6/30/yy value in it, it should not change it as the valid date in the EndDate field for telling us the end of the season date, it should have a 6/30/yy value. It's when a membership ends before the 6/30/yy season end date that we need a derived date to produce it from what's actually in the EndDate field that does not equal a 6/30/yy value.

[CJ_London]

OK, I think I understand what you require



try

DateEnd: switch(startdate between #07/01/2014# and #06/30/2015#,iif(enddate<#06/30/2015#,#06/30/2015#,enddate),startdate between #07/01/2015# and #06/30/2016#,iif(enddate<#06/30/2016#,#06/30/2016#,enddate))

[aellistechsupport]

Hi Ajax,
It did this on these records:

MembershipNumber	PaymentDate	StartDate	EndDate	InvoiceNumber	Date End
6270493	6/27/2014	7/1/2014	10/5/2015		10/5/2015
6041612	6/13/2014	7/1/2014	7/1/2016	0011555	7/1/2016
1216552	7/9/2015	6/1/2015	7/9/2015	0054543	7/9/2015
8908350	6/9/2015	6/9/2015	9/30/2015	0046347	9/30/2015
582710	6/8/2015	6/15/2015	7/6/2015		7/6/2015
1121744	6/25/2015	6/26/2015	9/23/2015	0050566	9/23/2015
1141914	6/25/2015	6/26/2015	9/23/2015	0050566	9/23/2015
1081687	6/25/2015	6/26/2015	9/23/2015	0050566	9/23/2015
6041738	6/26/2015	7/1/2015	7/1/2016	0052409	7/1/2016

[CJ_London]

What were you expecting?

first row 7/1/2014 so first part of the formula, enddate 10/5/2015 is not less than 6/30/15 so use enddate
2nd row 7/1/2014 so first part of the formula, enddate 7/1/2016 so not les than 6/30/15 so use enddate

and so on

[aellistechsupport]

How can we modify it that IF the EndDate equals a 6/30/yy to not change it?

[CJ_London]

I do get confused with the way you are describing things - enddate is not changed at any point, you are creating a new value called dateend - and you haven't answered my question - what were you expecting?

[aellistechsupport]

Sorry, IF there's a 6/30/yy in the EndDate field, the derived field "DateEnd" should keep what's in the EndDate, IF the EndDate field does NOT have a 6/30/yy THEN derive to a 6/30/yy per the logic.

if there's a 6/30/yy in the EndDate field already, that is presumed correct. It's only when it does not have a 6/30/yy we need it derived based on the logic as a "DateEnd" field. The DateEnd field be used to display the season end date. So if a 6/30/yy exists, it should display that in the DateEnd derived field, if not, do the logic.

Example record:
8748114 6/26/2015 6/30/2015 6/30/2016

It should show 6/30/2015 in the last column, the derived column "DateEnd"

1088736 7/1/2015 6/30/2015 6/30/2016
This is correct that it should display 6/30/2016 because the 1st column (StartDate) is 7/1/2015 whereas the above has the StartDate <7/1/2015


More examples: These already having 6/30/yy in 2nd column (EndDate) should display the actual EndDate in the 3rd column (DateEnd), otherwise do the logic
582710 6/1/2014 6/30/2015 6/30/2014
1209079 6/9/2013 6/30/2015 6/30/2013
1208792 6/9/2015 6/30/2016 6/30/2015

[CJ_London]

this is the code I provided

DateEnd: switch(startdate between #07/01/2014# and #06/30/2015#,iif(enddate<#06/30/2015#,#06/30/2015#,enddate),startdate between #07/01/2015# and #06/30/2016#,iif(enddate<#06/30/2016#,#06/30/2016#,enddate))

so using your example

Example record:
8748114 6/26/2015 6/30/2015 6/30/2016

It should show 6/30/2015 in the last column, the derived column "DateEnd"

so first part

startdate between #07/01/2014# and #06/30/2015#

startdate is 6/26/2015 - which is between these two dates - so do the next bit

iif(enddate<#06/30/2015#,#06/30/2015#,enddate)

enddate is 6/30/2015 - this is not less than 06/30/2015 - so use the enddate i.e. 6/30/2015 so I don't know where you are getting 6/30/2016 unless you have not used what I provided

[aellistechsupport]

iif(enddate<#06/30/2015#,#06/30/2015#,enddate)

It should be <> 6/30/yyyy then check the StartDate, if not just EndDate that already has a 6/30/yyyy value

Specifically:
If the StartDate is between 7/1/2014 - 6/30/2015 then the EndDate is not 6/30/yyyy, should be 6/30/2015
If the StartDate is between 7/1/2015 - 6/30/2016 then the EndDate is not 6/30/yyyy, should be 6/30/2016
Come this July, 2016 (7/1/2016) it's
If the StartDate is between 7/1/2016 - 6/30/2017 then the EndDate is not 6/30/yyyy, should be 6/30/2017

[CJ_London]

I'm afraid I just do not understand what you are saying

you say

It should be <> 6/30/yyyy then check the StartDate

and then you say

Specifically:
If the StartDate is between 7/1/2014 - 6/30/2015 then the EndDate is not 6/30/yyyy

which is the complete opposite

You have the basic construct of the way to do it, suggest you play around with it yourself, but regret I have to get on with earning a living and sorry I cannot help any more

Good luck with your project

[aellistechsupport]

Oops, sorry, I changed it and didn't reread the changes and now it doesn't make sense ...

Actual correction:
Specifically:
If the StartDate is between 7/1/2014 - 6/30/2015 AND the EndDate is not 6/30/yyyy THEN it should be 6/30/2015 ELSE the EndDate that has the 6/30/yyyy in the field already
If the StartDate is between 7/1/2015 - 6/30/2016 AND the EndDate is not 6/30/yyyy THEN it should be 6/30/2016 ELSE the EndDate that has the 6/30/yyyy in the field already
Come this July, 2016 (7/1/2016) it's
If the StartDate is between 7/1/2016 - 6/30/2017 AND the EndDate is not 6/30/yyyy THEN it should be 6/30/2017 ELSE the EndDate that has the 6/30/yyyy in the field already