Difficult If-statement

[kruger101]

I have a solution for my problem, I just want to see if there is an easier way. Here is my predicement:

From...........To..............c

0...............100............5
100............500............10
500...........1000...........15
1000.........3000............25
3000.........5000............30
5000........10000...........40
10000.......30000...........50
30000.......50000...........60

(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)

Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.

My current If-statement: Eventual Amount:
IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))

Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it.
Thanks a lot
Regards
kruger101

 

[Rich]

You could use a SelectCase statement, search here for examples

 

[raskew]

Hi -

The Switch() function would do the trick. Example from the debug (immediate) window:

y = 270000
z = switch(y<100, 5, y<500, 10,y<1000,15,y<3000,25,y<5000,30,y<10000,40,y<30000,50,y<50000,60,true,10*(y-50000)\5000 + 60)
? z
 500

HTH - Bob

 

[kruger101]

Thanks guys.
Is there a trunc function in Access? Trunc is where you throw away the numericals after the comma. eg: Trunc(2.9)=2 and Trunc(3.1)=3 and Trunc(1233.5)=1233

 

[kruger101]

Whoops sorry did a search and got my answer.

 

[raskew]

Hi -

Is there a trunc function in Access? Trunc is where you throw away the numericals after the comma. eg: Trunc(2.9)=2 and Trunc(3.1)=3 and Trunc(1233.5)=1233

The Int() function and the Int Operator (\) both do that. The Int Operator was included in my response

...true,10*(y-50000)\5000 + 60)

Did you try it and did it return what you were after?

Bob

 

[kruger101]

raskew
Yeah thanks did it did exactly what I wanted.
Cheers