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:
I want to return all records where in this field:
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]
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]
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.
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]
- Create the following field:
Code:
Expr1: Left([cam_sas.ayr_code],4)
- Add the following criterion:
Code:
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]
- Create the following field:
Code:
Expr1: IIf(Month(Now())>10,Left([cam_sas.ayr_code],4),Left([cam_sas.ayr_code],4)+1)
- Add the following criterion:
Code:
>=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.
Last edited: