IIf in query criterion (1 Viewer)

Hello,

I'm trying to use IIf in a query criterion, but not having any luck.

I have a field called 'ayr_code' which has values in the format:

• 2015/6
• 2014/5
• 2013/4
• 2012/3
• 2011/2
• 2010/1
• 2009/0
• 2008/9

I want to return all records where in this field:

• if the current month is November or December, the first four characters of 'ayr_code' are between five years ago and the current year;
• if the current month is between January and October, the first four characters of 'ayr_code' are between six years ago and last year;

So, in October 2015, I'd like to see records with an 'ayr_code' of 2014/5, 2013/4, 2012/3, 2011/2, 2010/1 or 2009/0. In November 2015, I'd like to see records with an 'ayr_code' of 2015/6, 2014/5, 2013/4, 2012/3, 2011/2 or 2010/1.

I've tried to do this using solution one below, but this is not working. I've gone for solution two, but I'd like to know why solution one doesn't work and if there is a way to amend it so that it does.

Any help would be much appreciated! Thanks in advance!

Solution one [preferable; not working]

1. Create the following field:
   Code:Copy to clipboard

   Expr1: Left([cam_sas.ayr_code],4)

2. Add the following criterion:
   Code:Copy to clipboard

   IIf(Month(Now())>10,>=Year(Now())-5 And <=Year(Now()),>=Year(Now())-6 And <=Year(Now())-1)

So this should get the first four characters of 'ayr_code' then apply one of two criteria on the results based on whether the current month is after October or not.

Solution two [non-preferable; working]

1. Create the following field:
   Code:Copy to clipboard

   Expr1: IIf(Month(Now())>10,Left([cam_sas.ayr_code],4),Left([cam_sas.ayr_code],4)+1)

2. Add the following criterion:
   Code:Copy to clipboard

   >=Year(Now())-5 And <=Year(Now())

This takes the first four characters of 'ayr_code' then if the current month is not after October it adds one to the result, after which it applies the criterion that the final output must be between this year and five years ago.

Sounds like a classic Fiscal Year problem. And, your encoded month/year isn't helping matters because its text and not a date. When its an actual date you can use Date functions (http://www.techonthenet.com/access/functions/) to help you do math and other comparisons with them.

My advice is to create a custom function in a module that you pass your ayr_code to and it returns the Fiscal Year that text value falls in. Then, for criteria in a query you can use simple math using today's date to determine which ones to include.

In a query it would look like this:

FY: get_FiscalYear([ayr_code])

With something like this in the criteria:

>=get_FiscalYear(Date()) - 5

Then in the module you would have this function:

Actually, I mistyped the criteria. Since you need it in ayr_code format to pass it to the function, the criteria would look like this:

>=(get_FiscalYear(Year(Date()) & "/" & Month(Date())) - 5)

I wonder if the original date field already exists.

A quick way to retrieve the year part is by using Val([FieldName]) - that will return 2015 in "2015/6". Then you can filter against this field.

you don't need a function, you can do it in SQL

Thanks for the replies. CJ_London, I'm using your solution (as it seemed the simplest). I put the following as a field.


This returned '-1' where the (academic) year was within the desired range and '0' if it was out of it. I then filtered on this output by putting '-1' as a criterion. Then I added 'ayr_code' as a separate field and hid the field being used to filter. Perfect!

Here's how I was envisioning it (just a different take on the subject):

You can also create functions for Year(Now()) and Month(Now()) and perhaps call them CurrYear() and CurrMonth() respectively, then change your code to look like this:
... the engine is clever enough not to re-run those functions more than once because, the function returns the same value and it has no parameters. There are definitely performance benefits.

vbaInet - thanks, that's even simpler!

I don't think we said, welcome to the forum!