IIF Statement to return multiple values for a field - JohnW

[JohnW]

I need to calculate multiple values for a field called CSP that will be based
on a calculated field called Days. Days is calculated by using
DateDifference which takes a Today Date field minus an inputted field, Start
Date. The CSP needs to return a decimal value or if none return a value of
zero. I am placing the expression in a query and have tried....

CSP: =IFF(AND([Days}>=365,[Days]<730),".05","0") which should return a value
of .05

CSP will be different for different values of the "Days" field so I want to
use many IFF expressions for the same field. For example using the above
example the next expression would be

CSP: =IFF(AND([Days}>=730,[Days]<1095),".10","0") which should return a
value of .10

Can I do this? And what would be the proper expression to return these
values?

Thanks for the help.

 

[Sylvain Lafontaine]

You put the second IIS inside the first one using the ELSE (or FALSE) part:

CSP:
=IFF(AND([Days}>=365,[Days]<730),".05",IFF(AND([Days}>=730,[Days]<1095),".10","0"))

For much more complex suite of conditions, you can create a VBA function
(taking only variant parameters and returning a variant value if I remember
correctly?) and use it instead.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[John Spencer]

CSP: IIF([Days]>=365 AND [Days]<730),.05,0)
which should return a value of .05

You can only nest up to seven IIf (IF I recall correctly). So you might
need to do this another way.

CSP: IIF([Days]>=365 AND [Days]<730),.05,IIF([Days}>=730 AND
[Days]<1095),.10,0)

You might consider using the VBA Switch function.
SWITCH([Days]>=365 AND [Days]<730,.05
,[Days]>=730 AND [Days]<1095,.10
True,0)

Although if the Progression is regular - that is CSP increates by .05
every time you pass a threshold of 365 days - you could do this with a
math formula.

If the progression is not regular, you should consider a samll table
with the range start, range end, and the CSP for the range. Then you
could get the value in a query or elsewhere using the DLookup function
or by joining the table in a query to the appropriate ranges using a
non-equi join.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JohnW]

John S

The Switch command worked great, exactly what I needed. Thanks for the help.

--
JCW

CSP: IIF([Days]>=365 AND [Days]<730),.05,0)
which should return a value of .05

You can only nest up to seven IIf (IF I recall correctly). So you might
need to do this another way.

CSP: IIF([Days]>=365 AND [Days]<730),.05,IIF([Days}>=730 AND
[Days]<1095),.10,0)

You might consider using the VBA Switch function.
SWITCH([Days]>=365 AND [Days]<730,.05
,[Days]>=730 AND [Days]<1095,.10
True,0)

Although if the Progression is regular - that is CSP increates by .05
every time you pass a threshold of 365 days - you could do this with a
math formula.

If the progression is not regular, you should consider a samll table
with the range start, range end, and the CSP for the range. Then you
could get the value in a query or elsewhere using the DLookup function
or by joining the table in a query to the appropriate ranges using a
non-equi join.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I need to calculate multiple values for a field called CSP that will be based
on a calculated field called Days. Days is calculated by using
DateDifference which takes a Today Date field minus an inputted field, Start
Date. The CSP needs to return a decimal value or if none return a value of
zero. I am placing the expression in a query and have tried....

CSP: =IFF(AND([Days}>=365,[Days]<730),".05","0") which should return a value
of .05

CSP will be different for different values of the "Days" field so I want to
use many IFF expressions for the same field. For example using the above
example the next expression would be

CSP: =IFF(AND([Days}>=730,[Days]<1095),".10","0") which should return a
value of .10

Can I do this? And what would be the proper expression to return these
values?

Thanks for the help.