Here's a variation on what John Spencer suggested; it differs from your
Iif() when the number of years equals 30.
Since I didn't know where your [DATE] field came from, in these examples
I just assumed it referred to today's date, 3/30/2006.
Suppose you have a collection of [HPDATE] values in a Table called
[Samples]:
[Samples] Table Datasheet View:
Samples_ID HPDATE
----------- ---------
-1316851320 1/1/1970
-271861666 1/1/2006
-269230969 1/1/2005
-12201226 3/29/1976
33364739 1/1/1980
79629756 1/1/2004
172955967 3/31/1976
535700713 3/30/1976
1055674893 1/1/1996
1669470794 1/1/1985
The following Query shows the results of both your Iif() function and a
modified version of John's Switch(), in which I eliminated the last
choice (the one for over 30 years).
[Q_IIFsAndSwitch] SQL:
SELECT Int(Now()) AS [DATE], Samples.HPDATE,
IIf([DATE]<DateAdd("yyyy",+1,[HPDATE]),0.04,
IIf([DATE]<DateAdd("yyyy",+2,[HPDATE]),0.05,
IIf([DATE]<DateAdd("yyyy",+7,[HPDATE]),0.07,
IIf([DATE]<DateAdd("yyyy",+15,[HPDATE]),0.09,
IIf([DATE]<DateAdd("yyyy",+24,[HPDATE]),0.11,
IIf([DATE]<DateAdd("yyyy",+30,[HPDATE]),0.13,
IIf([DATE]>DateAdd("yyyy",+30,[HPDATE]),0.15,
Null))))))) AS [PERCENT],
Val(Switch(
[DATE]<DateAdd("yyyy",+1,[HPDATE]),0.04,
[DATE]<DateAdd("yyyy",+2,[HPDATE]),0.05,
[DATE]<DateAdd("yyyy",+7,[HPDATE]),0.07,
[DATE]<DateAdd("yyyy",+15,[HPDATE]),0.09,
[DATE]<DateAdd("yyyy",+24,[HPDATE]),0.11,
[DATE]<DateAdd("yyyy",+30,[HPDATE]),0.13,
True,0.15)) AS PERCENT2
FROM Samples
ORDER BY Samples.HPDATE;
The results are pretty similar, except for the value for 3/30/1976.
[Q_IIFsAndSwitch] Query Datasheet View:
DATE HPDATE PERCENT PERCENT2
---------- --------- -------- --------
3/30/2006 1/1/1970 15.00% 15.00%
3/30/2006 3/29/1976 15.00% 15.00%
3/30/2006 3/30/1976 15.00%
3/30/2006 3/31/1976 13.00% 13.00%
3/30/2006 1/1/1980 13.00% 13.00%
3/30/2006 1/1/1985 11.00% 11.00%
3/30/2006 1/1/1996 9.00% 9.00%
3/30/2006 1/1/2004 7.00% 7.00%
3/30/2006 1/1/2005 5.00% 5.00%
3/30/2006 1/1/2006 4.00% 4.00%
However, maintaining SQL like that can be a pain... Suppose the cutoff
values change? My suggestion would be to add a Table to your database
that contains the values that you stored in your tree of nested Iif()
function calls. Then you wouldn't need to worry about how deep the nest
is. I did do something hokey -- I used 999 as a surrogate for "longer
than any time period you'll have in your database". It'll fail if you
store some longer time there, but in the meantime it simplifies the coding.
[Percents] Table Datasheet View:
Years Percents
----- --------
1 4.00%
2 5.00%
7 7.00%
15 9.00%
24 11.00%
30 13.00%
999 15.00%
Now, using the "Now()" function (which returns today's date) as a
stand-in for your [DATE] field, we define a Query which will calculate
the number of years for each of the dates listed in the [Samples] Table.
[Q_Years] SQL:
SELECT S.HPDATE AS HPDATE1,
Min(P.Years) AS Period
FROM Samples AS S, Percents AS P
WHERE (((DateAdd("yyyy",P![Years],[HPDATE]))
GROUP BY S.HPDATE
ORDER BY S.HPDATE;
Each of the numbers of years (except 999) is rounded up to one of the
numbers in your Iif() expression.
[Q_Years] Query Datasheet View:
HPDATE1 Period
---------- ------
1/1/1970 999
3/29/1976 999
3/30/1976 999
3/31/1976 30
1/1/1980 30
1/1/1985 24
1/1/1996 15
1/1/2004 7
1/1/2005 2
1/1/2006 1
Now that we know the number of years for each one, we can return the
corresponding percentage, using the following Query:
[Q_From_Years] SQL:
SELECT Int(Now()) AS [DATE],
S.HPDATE, P.Percents
FROM Samples AS S
INNER JOIN (Q_Years AS QY
INNER JOIN Percents AS P
ON QY.Period = P.Years)
ON S.HPDATE = QY.HPDATE1
ORDER BY S.HPDATE;
The results are the same as what we got from the [Q_IIFsAndSwitch] Query
(using my modified version of Switch()).
[Q_From_Years] Query Datasheet View:
DATE HPDATE Percents
--------- --------- ---------
3/30/2006 1/1/1970 15.00%
3/30/2006 3/29/1976 15.00%
3/30/2006 3/30/1976 15.00%
3/30/2006 3/31/1976 13.00%
3/30/2006 1/1/1980 13.00%
3/30/2006 1/1/1985 11.00%
3/30/2006 1/1/1996 9.00%
3/30/2006 1/1/2004 7.00%
3/30/2006 1/1/2005 5.00%
3/30/2006 1/1/2006 4.00%
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
John said:
Switch allows you to write pairs of criteria and results. It then takes the
first true criteria and returns the value. So you could write that as the
following (all on one line)
Switch(
[DATE]<DateAdd("yyyy",+1,[HPDATE]),0.04,
[DATE]<DateAdd("yyyy",+2,[HPDATE]),0.05,
[DATE]<DateAdd("yyyy",+7,[HPDATE]),0.07,
[DATE]<DateAdd("yyyy",+15,[HPDATE]),0.09,
[DATE]<DateAdd("yyyy",+24,[HPDATE]),0.11.
[DATE]<DateAdd("yyyy",+30,[HPDATE]),0.13,
DATE]>DateAdd("yyyy",+30,[HPDATE]),0.15,
True, Null)
Not sure what the switch function is. You are right about the parentheses
and
commas, but it has happened too many times after five for me to think its
that, I still think its something built in. I am new to this and really
am
not sure how you would write vba around this:
PERCENT:
IIf([DATE]<DateAdd("yyyy",+1,[HPDATE]),0.04,IIf([DATE]<DateAdd("yyyy",+2,[HPDATE]),0.05,IIf([DATE]<DateAdd("yyyy",+7,[HPDATE]),0.07,IIf([DATE]<DateAdd("yyyy",+15,[HPDATE]),0.09,IIf([DATE]<DateAdd("yyyy",+24,[HPDATE]),0.11,IIf([DATE]<DateAdd("yyyy",+30,[HPDATE]),0.13,IIf([DATE]>DateAdd("yyyy",+30,[HPDATE]),0.15,Null)))))))
I am much better with formulas. Thanks
:
I think the max may be seven, but if I had that many, I would write a VBA
function to handle the situation or use the Switch function instead.
I don't know what error you were getting - you didn't say - but when I
try
do nested IIf's I always have trouble getting the parentheses and commas
to
all match up correctly. Adding one at a time, often helps me to avoid
the
syntax errors.
I have a really weird problem and not sure why. I have a calculation
doing
nested iif's seven of them. I kept getting an error message and it
pointed
to the fifth one, it was right, so I cut it short and did 4 to start
and
as
long as I ran the query after each addition until I finally had all
seven
nested it works. But it would not let me run them on the first go. Is
this
normal and how many nested iif's can you do. I know the old excel
would
only let you do seven is there a limit in access? Thanks
