IIf expression Access

[andrew.abaye]

Good day everyone. I am trying to use the Access iif expression to calculate the basic salary of employees based on the employment type but it is not working. See my iif statement below. The field that has the value is Amount while the field that has the employment type is duty_type.

IIf([duty_type]='Adjunct',[amount]*0.33),IIf([duty_type]='Full Time',[amount]*1),IIf([duty_type]='Contructual',[amount]*1),IIf([duty_type]='Sabbatical',[amount]*1)))))

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Gasman]

Please do not post question in the Introduction forum.
Post in the respective forum, in this case Modules & VBA

 

[GaP42]

Is not working - how? Describe the error, state the error.
The IIf test needs to specifiy the value if false as well as if true. You last test does not:
IIf([duty_type]='Sabbatical',[amount]*1)

 

[plog]

Not working how? Incorrect results? Syntax error? Other error? No results? Caused your computer to catch in fire?

I do see a possible logical error but that wouldn't cause it to 'not work'. You've explicitly accounted for 4 cases, but when none of those cases are met that code will return NULL. Is that what you expect it to return when none of those four cases are met?

 

[andrew.abaye]

Not working how? Incorrect results? Syntax error? Other error? No results? Caused your computer to catch in fire?

I do see a possible logical error but that wouldn't cause it to 'not work'. You've explicitly accounted for 4 cases, but when none of those cases are met that code will return NULL. Is that what you expect it to return when none of those four cases are met?

It returned syntax error. Could you provide me a workable code. I am new to Access Expression.

 

[andrew.abaye]

Is not working - how? Describe the error, state the error.
The IIf test needs to specifiy the value if false as well as if true. You last test does not:
IIf([duty_type]='Sabbatical',[amount]*1)

 

[andrew.abaye]

This is what I came up with based on your example and it returned the expression you entered contains invlaid syntx. IIf([duty_type]='Sabbatical',[amount]*1),IIf([duty_type]='Adjunct',[amount]*0.33),IIf([duty_type]='Full Time',[amount]*1))))

 

[Gasman]

If you only have 4 types, then you can make the False part amount * 1 ? then you just need one IIF ?

 

[andrew.abaye]

If you only have 4 types, then you can make the False part amount * 1 ?

Yes

 

[Gasman]

Please start using code tags

IIf([duty_type]='Adjunct',[amount]*0.33,[Amount])

 

[GaP42]

You will continue to get invalid syntax if you do not form the expression correctly, no matter what order you place your iif tests.
As said, in the last test you need to state what happens if it is false. You have not yet done that.
However as @Gasman states - your nested iifs can be reduced to one as all but one result in the same outcome.

 

[plog]

IIf([duty_type]='Sabbatical',[amount]*1),IIf([duty_type]='Adjunct',[amount]*0.33),IIf([duty_type]='Full Time',[amount]*1))))

Syntactically you've got too many right parenthesis: 6 compared to just 3 left parenthesis.

Logically, if you only have 2 results you only need to perform one test too see which of the results to provide. No need test for each individual possibility. That's what gasmans code does and what you should use.

 

[ebs17]

In keeping with the database, it would be best to resolve these if-then orgies by creating an additional table for dyty_types with a factor included and linking it to the data table in a query.

This design is then simpler, clearer, easier to maintain and expandable.

 

[andrew.abaye]

Thank you all Folks. I figured my error after reading your responses and I got my expression working. This is what worked
=IIf( [duty_type] ="Full Time",( [amount] *1),IIf( [duty_type] ="Adjunct", ([amount] *0.33),IIf( [duty_type] ='Sabbatical',( [amount] *1),"Amount Not Found")))

 

[Gasman]

 

[GPGeorge]

Thank you all Folks. I figured my error after reading your responses and I got my expression working. This is what worked
=IIf( [duty_type] ="Full Time",( [amount] *1),IIf( [duty_type] ="Adjunct", ([amount] *0.33),IIf( [duty_type] ='Sabbatical',( [amount] *1),"Amount Not Found")))

Okay, but do consider implementing a table-driven solution that does two things for your future success.

• First, a table would eliminate the need for the multi-part IIf() expression, simplifying code and enhancing readability
• Second, a table would support expansion of the duty-type options so that you could quickly and easily add a fifth duty_type with no changes to an expression in SQL or VBA.

 

[Gasman]

Okay, but do consider implementing a table-driven solution that does two things for your future success.

• First, a table would eliminate the need for the multi-part IIf() expression, simplifying code and enhancing readability
• Second, a table would support expansion of the duty-type options so that you could quickly and easily add a fifth duty_type with no changes to an expression in SQL or VBA.

We cannot get the member to simplify his IIF even , I doubt they are going to take the trouble to go the table route, which I agree is much more flexible.
As I mentioned in another thread "You can take a horse to water ...... "

 

[ebs17]

amount *1 versus "Amount Not Found"

In one case, the result is a number with decimal places (double), the other is visible text.

The result is text as the data type for the calculated field. It may be sufficient to look at, but if you want to do further mathematical calculations with it or just sort in the classic way, you will experience further surprises.
Think again.
Or: People only learn through pain.

 

[Gasman]

Why do you even need to multiply by one?