Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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/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
tom_optimum
Asked:
tom_optimum
1 Solution
 
John EastonDirectorCommented:
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
 
Haris DjulicCommented:
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

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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)))))

Open in new window

0
 
tom_optimumAuthor Commented:
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
 
mlmccCommented:
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))))))

Open in new window


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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now