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.