iff statment

[istone]

Hi all,
can anyone help me make this formula work?
it's a query that has one column called: Computed assest balance.

I am trying to say: if Computed assest balance is between 0 and 30,000,000 then Computed assest balance*0.001/4 else Computed assest balance is between 30,000,000 and 40,000,000 then Computed assest balance*0.0008/4 etc

I tried the follwing but it's giving me all kinds of errors:


iif([Computed assest balance]>=0 and [Computed assest balance]<=30000000,[Computed assest balance]*0.001/4),
iif([Computed assest balance]>=30000000 and [Computed assest balance]<=40000000,[Computed assest balance]*0.0008/4),
iif([Computed assest balance]>=40000000 and [Computed assest balance]<=50000000,[Computed assest balance]*0.0006/4),
iif([Computed assest balance]>=50000000),[Computed assest balance]*0.0005/4)

thanks in advance

 

[lespaul]

iif([Computed assest balance] > 50000000, [Computed assest balance] * 0.0005/4, iif([computed assest balance] > 40000000, [Computed assest balance] * 0.0006/4, iif([Computed assest balance] > 30000000, [Computed assest balance] * 0.0008/4, [Computed assest balance] * 0.001/4)))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[istone]

THANK YOU

 

[dhookom]

I wouldn't use any expression with more than 1 IIf(). I would either create a user-defined function or range value lookup table or both.

Hard-coded values like 50000000, 0.0005/4, ... will most likely change over time. You should never have to go back to edit a complex expression in a query. This would be much more maintainable in data or a user-defined function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]