Solved

# Access nested Iff between dates

Posted on 2014-09-25
224 Views
Hi,

I would like a query in Access that does the following logic:

If ([CTFImport.DOB] >=#01/09/2014#) And [CTFImport.DOB]<=#31/08/2015#) then -5
else If ([CTFImport.DOB] >=#01/09/2013#) And [CTFImport.DOB]<=#31/08/2014#) then -4
else If ([CTFImport.DOB] >=#01/09/2012#) And [CTFImport.DOB]<=#31/08/2013#) then -3
else If ([CTFImport.DOB] >=#01/09/2011#) And [CTFImport.DOB]<=#31/08/2012#) then -2
else If ([CTFImport.DOB] >=#01/09/2010#) And [CTFImport.DOB]<=#31/08/2011#) then -1
else CTFImport.NCyearActual

I can do this in Crystal Reports but I am struggling in Access.. can you help please?

Thanks,

Tom
0
Question by:tom_optimum
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 11

Accepted Solution

John Easton earned 500 total points
ID: 40343817
I think you will need a series of IIF statement which use the Between comparator.

For example:
IIF([CTFImport.DOB] Between #01/09/2014# AND #31/08/2015#,-5, IIF([CTFImport.DOB] Between #01/09/2013# AND #31/08/2014#,-4, IIF([CTFImport.DOB] Between #01/09/2012# AND #31/08/2013#,-3, IIF([CTFImport.DOB] Between #01/09/2011# AND #31/08/2012#,-2, IIF([CTFImport.DOB] Between #01/09/2010# AND #31/08/2011#,-1,CTFImport.NCyearActual)))))

Alternately you could write your own function is VBA and use that.
0

LVL 15

Expert Comment

ID: 40343821
try:

``````SELECT
Switch(
([CTFImport.DOB] >=#01/09/2014#) And [CTFImport.DOB]<=#31/08/2015#), -5,
([CTFImport.DOB] >=#01/09/2013#) And [CTFImport.DOB]<=#31/08/2014#) , -4,
([CTFImport.DOB] >=#01/09/2012#) And [CTFImport.DOB]<=#31/08/2013#), -3,
([CTFImport.DOB] >=#01/09/2011#) And [CTFImport.DOB]<=#31/08/2012#), -2,
([CTFImport.DOB] >=#01/09/2010#) And [CTFImport.DOB]<=#31/08/2011#) , -1
) AS result
from tabel_name
``````
0

LVL 24

Expert Comment

ID: 40343833
``````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)))))
``````
0

Author Closing Comment

ID: 40343863
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
0

LVL 100

Expert Comment

ID: 40343865
WIth the addition of one test, you can eliminate 5 tests from the formula.

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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
###### Suggested Courses
Course of the Month8 days, 21 hours left to enroll