IIF Functions (1 Viewer)

[Cam]

Hi all,

Im new to access, after learning basics Iam trying to build my first DB.

Having some troubles with the IIF synax. Iam placing the formula

=IIf([Total]<=(200),[Total]*(0.7)) & IIf([Total]>(201)&[Total]<=(500),[Total]-(200)*(1)+(140)) & IIf([Total]>(501),[Total]-(500)*(1.2)+(300)+(140))

In the form data control source of a field. The first 2 statement seems to work, but the last statement results in some crazzy figures.

Any help would be much appreicated or work around

:banghead:

Kind
Regards
Cam

 

[pr2-eugin]

Hello Cam, Welcome to AWF

Show an example what you are trying to do, the formula does not make any sense.

 

[CazB]

IIF statements need to have a logical test, a value if true, and a value if false. If the first logical test evaluates to false, then it will move on to the 'value if false' part, which can be another IIIF statement...
I THINK what you need is the statement below:


=IIf([Total]<=200,[Total]*0.7,IIf([Total]<=500,([Total]-200)+140,(([Total]-500)*1.2)+300+140))


What this says is:
If the total is less than or equal to 200 (logical test), multiply total by 0.7 (value if true). If it's not less than 200, then your 'value if false' comes into play - which is a second Iif statement.

So, if it's not less than 200 but it is less than or equal to 500, work out the difference between 200 and the Total, then add on 140.

If it's not less than or equal to 500, then your second 'value if false' comes into play:

otherwise, work out the difference between 500 and the Total, then add on 300 and then add 140

Hope that explains it?

 

[Cam]

Thank you Casb and pr2 eugin for the prompt reply, I have decided to go learn visual basic, b/c using an expression on a control source is unbound. Writing a code to do event procedure will also send the form data back to teh database.

Thanks again guys!