Nested IIFs?

[aellistechsupport]

I am trying to return the result to where:
If the ClassID = "ADMINFEE" AND the DateEnd field is NULL
AND the PaidYear = this year then return as
EndDate
1. 6/30/thisyear


Else
2. 6/30/next year
Else
3. What's in DateEnd

This is what I have but it's incorrect.

EndDate: IIf([ClassID]="ADMINFEE" And IsNull([DateEnd]),IIf([PaidYear]=Year(Date()),DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)),[DateEnd])

[ssanfu]

This is what I have but it's incorrect.

WHY is it incorrect?

Is the field "[ClassID]" a text type or a number type field?

Is the field "[PaidYear]" a text type or a number type field?

[aellistechsupport]

WHY is it incorrect?

Is the field "[ClassID]" a text type or a number type field?

Is the field "[PaidYear]" a text type or a number type field?

ClassID = "ADMINFEE" is text type field
PaidYear = 2015 is a number field (converting PaymentDate with Year([PaymentDate]) as PaidYear

[ranman256]

when you have uses lots of nested IF's then its time to either:

make a lookup table to join to the data (this is the IF)
or
make a function

[ssanfu]

Still don't know WHY the IIF() statement is incorrect.

What is the data in 1 or 2 records?

What does the query return for those records?


Maybe post your dB?

[aellistechsupport]

Oh, right, why I posted this as incorrect is that the result showed as such:

A record has a paymentdate of 5/1/2015, so the PaidYear=2015
The EndDate displayed (where the formula is) 6/30/2017, instead of 6/30/2016

[ssanfu]

So if the PaidYear is 2015, the end date should be in 2016?

Maybe change
DateSerial(Year(Date())+1,6,30)),

to
DateSerial(PaidYear+1,6,30)),


Since the calculation is giving results, you just have to tweak it until it returns the correct results in all cases.

[aellistechsupport]

Will try that.

But see now where I didn't have it correct, this says if PaidYear = thisyear (2016), then 6/30/2016, else 2017
IIf([PaidYear]=Year(Date()),DateSerial(Year(Date()),6,30),DateSe rialYear(Date())+1,6,30))

this should now says if PaidYear = lastyear (2015), then 6/30/2016, else 2017
IIf([PaidYear]=Year(Date())-1,DateSerial(Year(Date()),6,30),DateSerialYear(Date ())+1,6,30))