tom_optimum
asked on
Access nested Iff between dates
Hi,
I would like a query in Access that does the following logic:
If ([CTFImport.DOB] >=#01/09/2014#) And [CTFImport.DOB]<=#31/08/20 15#) then -5
else If ([CTFImport.DOB] >=#01/09/2013#) And [CTFImport.DOB]<=#31/08/20 14#) then -4
else If ([CTFImport.DOB] >=#01/09/2012#) And [CTFImport.DOB]<=#31/08/20 13#) then -3
else If ([CTFImport.DOB] >=#01/09/2011#) And [CTFImport.DOB]<=#31/08/20 12#) then -2
else If ([CTFImport.DOB] >=#01/09/2010#) And [CTFImport.DOB]<=#31/08/20 11#) then -1
else CTFImport.NCyearActual
I can do this in Crystal Reports but I am struggling in Access.. can you help please?
Thanks,
Tom
I would like a query in Access that does the following logic:
If ([CTFImport.DOB] >=#01/09/2014#) And [CTFImport.DOB]<=#31/08/20
else If ([CTFImport.DOB] >=#01/09/2013#) And [CTFImport.DOB]<=#31/08/20
else If ([CTFImport.DOB] >=#01/09/2012#) And [CTFImport.DOB]<=#31/08/20
else If ([CTFImport.DOB] >=#01/09/2011#) And [CTFImport.DOB]<=#31/08/20
else If ([CTFImport.DOB] >=#01/09/2010#) And [CTFImport.DOB]<=#31/08/20
else CTFImport.NCyearActual
I can do this in Crystal Reports but I am struggling in Access.. can you help please?
Thanks,
Tom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IIf([CTFImport.DOB] >=#01/09/2014# And [CTFImport.DOB]<=#31/08/2015#,-5,
IIf([CTFImport.DOB] >=#01/09/2013# And [CTFImport.DOB]<=#31/08/2014#,-4,
IIf([CTFImport.DOB] >=#01/09/2012# And [CTFImport.DOB]<=#31/08/2013#,-3,
IIf([CTFImport.DOB] >=#01/09/2011# And [CTFImport.DOB]<=#31/08/2012#,-2,
IIf([CTFImport.DOB] >=#01/09/2010# And [CTFImport.DOB]<=#31/08/2011#,-1,
CTFImport.NCyearActual)))))
ASKER
This worked perfect - thank you.
I didn't try the others - I thought it was fair to start with the first suggested one and work down.. but the first one worked great.
Thanks everyone for looking at this for me.
Cheers
Tom
I didn't try the others - I thought it was fair to start with the first suggested one and work down.. but the first one worked great.
Thanks everyone for looking at this for me.
Cheers
Tom
WIth the addition of one test, you can eliminate 5 tests from the formula.
Perhaps better from a performance standpoint.
Since by the fieldname these are probably dates of birth the first test could be eliminated since there shouldn't be any in the data that are greater than 1 Sep 2015.
mlmcc
Perhaps better from a performance standpoint.
IIF( [CTFImport.DOB]>=#01/09/2015#,CTFImport.NCyearActual,
IIf([CTFImport.DOB] >=#01/09/2014# ,-5,
IIf([CTFImport.DOB] >=#01/09/2013# ,-4,
IIf([CTFImport.DOB] >=#01/09/2012# ,-3,
IIf([CTFImport.DOB] >=#01/09/2011# ,-2,
IIf([CTFImport.DOB] >=#01/09/2010# ,-1,
CTFImport.NCyearActual))))))
Since by the fieldname these are probably dates of birth the first test could be eliminated since there shouldn't be any in the data that are greater than 1 Sep 2015.
mlmcc
Open in new window