Nested IIF in Expression of Calculated Table Field

[Jonny]

I can make one condition expression , as shown, however I have more than 10 conditions.
I do prefer to build them as a nested IIF statement in VBA.

What's the right way to do that?

 

[theDBguy]

Hi. It looks like you're trying to create a calculated field. If so, you can't use VBA at the table level. You actually have a limited amount of functions available to you at this point. What exactly are you trying to calculate?

 

[Jonny]

I'm adding records into employee table. Where new employee is engineer he gets a degree "4" (calculated field).
If new employee architect , het gets "5" , e.t.c..

There are many IIfs..

 

[theDBguy]

I'm adding records into employee table. Where new employee is engineer he gets a degree "4" (calculated field).
If new employee architect , het gets "5" , e.t.c..

There are many IIfs..

Hi. I believe IIf() statements can only have up to seven (7) layers (or was it 14?). If you have more than that, and it's probably a better approach anyway, it might be easier to create a separate table of these values and simply link the two tables together in a query.

 

[Jonny]

Assuming it has 7~14 layers, may I build the with CASE statement in VBA as it more readable?

 

[theDBguy]

Assuming it has 7~14 layers, may I build the with CASE statement in VBA as it more readable?

Again, though, VBA is not available at the table level, so you can't use it for Calculated fields. I mean, you could try with just one or two options in VBA and see if you can use it in the table. If you can, you can finish building it. If you can't, then you know you didn't waste your time building something you can't use.

 

[Jonny]

Thank you , understand, I will try that.