IIf statements (what is limit & how do i get around this)

  • Thread starter Thread starter _Bigred
  • Start date Start date
B

_Bigred

(Access 2000)

I am trying to use the following expression on a form for a unbound box.
Unfortunately when I try to close the expression builder, it gives the error
message that my expression is too complex.

=IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",([Under13]+5.7),IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",([Under13]+4.50),IIf([Class Title]="ANIMAL HEALTH
CONSULTANT",([Under13]+1.60,iif([Class Title]="FOOD SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="MEAT SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="FOOD SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="FOOD SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MEAT SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="MEAT SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MULTIPLE PRODUCTS GRADER-ENTRY"
,([Under13]+1.50, iif([Class Title]="MULTIPLE PRODUCTS GRADER-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES INSP-DEV"
,([Under13]+1.50, iif([Class Title]="WEIGHTS&MEASURES INSP-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES TECH SPEC"
,([Under13]+1.60))))))))))))))


How do I get around this so my formula (expression) works properly????

TIA,
_Bigred
 
Bigred,

There is a limit of 7 nested IIf() functions. I think you slightly
exceeded this. <g>

I notice that you have a number of Class Title categories where the
outcome is the same, so you could simplify it to less than 7 nested
levels, using IIf or even simpler using Switch, e.g.

=[Under13]+Switch([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",5.7,[Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",4.5,[Class Title] In("ANIMAL HEALTH CONSULTANT","FOOD
SAFETY CONSULTANT","MEAT SAFETY CONSULTANT","WEIGHTS&MEASURES TECH
SPEC"),1.6,... etc)

However, I would recommend a completely different approach. Make a
table, if you haven't already got one, with the Class Title field, and
add a field with the ThingFactor field (whatever is the meaning of the
5.7, 4.5, 1.6 etc figures in your calculation). Then, add this table to
the query that the form is based on, if it's not already there, joined
to your existing data on the Class Title field, and then all you need in
your expression is...
=[Under13]+[ThingFactor]
 
Thanks Steve I will give that I try and post up my results.

Thanks,
_Bigred


Steve Schapel said:
Bigred,

There is a limit of 7 nested IIf() functions. I think you slightly
exceeded this. <g>

I notice that you have a number of Class Title categories where the
outcome is the same, so you could simplify it to less than 7 nested
levels, using IIf or even simpler using Switch, e.g.

=[Under13]+Switch([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",5.7,[Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",4.5,[Class Title] In("ANIMAL HEALTH CONSULTANT","FOOD
SAFETY CONSULTANT","MEAT SAFETY CONSULTANT","WEIGHTS&MEASURES TECH
SPEC"),1.6,... etc)

However, I would recommend a completely different approach. Make a
table, if you haven't already got one, with the Class Title field, and
add a field with the ThingFactor field (whatever is the meaning of the
5.7, 4.5, 1.6 etc figures in your calculation). Then, add this table to
the query that the form is based on, if it's not already there, joined
to your existing data on the Class Title field, and then all you need in
your expression is...
=[Under13]+[ThingFactor]

--
Steve Schapel, Microsoft Access MVP


_Bigred said:
(Access 2000)

I am trying to use the following expression on a form for a unbound box.
Unfortunately when I try to close the expression builder, it gives the error
message that my expression is too complex.

=IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",([Under13]+5.7),IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",([Under13]+4.50),IIf([Class Title]="ANIMAL HEALTH
CONSULTANT",([Under13]+1.60,iif([Class Title]="FOOD SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="MEAT SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="FOOD SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="FOOD SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MEAT SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="MEAT SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MULTIPLE PRODUCTS GRADER-ENTRY"
,([Under13]+1.50, iif([Class Title]="MULTIPLE PRODUCTS GRADER-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES INSP-DEV"
,([Under13]+1.50, iif([Class Title]="WEIGHTS&MEASURES INSP-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES TECH SPEC"
,([Under13]+1.60))))))))))))))


How do I get around this so my formula (expression) works properly????

TIA,
_Bigred
Click to expand...
Click to expand...
 
Hello Steve,

I have created the table "YR2PVR" with the following fields "Class Title"
& "PRV".
- Short description is that the PRV is a value that a particular job
classification in state government will receive once OUR contract is
finalized.

If I open up my query in design,
1)then add thE "YR2PVR" table to the query.
2) then dropped the two fields listed above into the query.
- what fields would i set the relationship between (main table in db has a
"Class Title" field)?

I can't seem to figure it out, even though the directions sound straight
forward.

TIA,
_Bigred


Steve Schapel said:
Bigred,

There is a limit of 7 nested IIf() functions. I think you slightly
exceeded this. <g>

I notice that you have a number of Class Title categories where the
outcome is the same, so you could simplify it to less than 7 nested
levels, using IIf or even simpler using Switch, e.g.

=[Under13]+Switch([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",5.7,[Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",4.5,[Class Title] In("ANIMAL HEALTH CONSULTANT","FOOD
SAFETY CONSULTANT","MEAT SAFETY CONSULTANT","WEIGHTS&MEASURES TECH
SPEC"),1.6,... etc)

However, I would recommend a completely different approach. Make a
table, if you haven't already got one, with the Class Title field, and
add a field with the ThingFactor field (whatever is the meaning of the
5.7, 4.5, 1.6 etc figures in your calculation). Then, add this table to
the query that the form is based on, if it's not already there, joined
to your existing data on the Class Title field, and then all you need in
your expression is...
=[Under13]+[ThingFactor]

--
Steve Schapel, Microsoft Access MVP


_Bigred said:
(Access 2000)

I am trying to use the following expression on a form for a unbound box.
Unfortunately when I try to close the expression builder, it gives the error
message that my expression is too complex.

=IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-ENTRY",([Under13]+5.7),IIf([Class Title]="ANIMAL HEALTH
INSPECTOR-OBJ",([Under13]+4.50),IIf([Class Title]="ANIMAL HEALTH
CONSULTANT",([Under13]+1.60,iif([Class Title]="FOOD SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="MEAT SAFETY CONSULTANT"
,([Under13]+1.60,iif([Class Title]="FOOD SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="FOOD SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MEAT SAFETY INSPECTOR-ENTRY"
,([Under13]+1.50, iif([Class Title]="MEAT SAFETY INSPECTOR-OBJ"
,([Under13]+1.55, iif([Class Title]="MULTIPLE PRODUCTS GRADER-ENTRY"
,([Under13]+1.50, iif([Class Title]="MULTIPLE PRODUCTS GRADER-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES INSP-DEV"
,([Under13]+1.50, iif([Class Title]="WEIGHTS&MEASURES INSP-OBJ"
,([Under13]+1.55, iif([Class Title]="WEIGHTS&MEASURES TECH SPEC"
,([Under13]+1.60))))))))))))))


How do I get around this so my formula (expression) works properly????

TIA,
_Bigred
Click to expand...
Click to expand...
 
Bigred,

If I understand you correctly, you would join the YR2PVR table to your
main table on the Class Title field from both... these are the fields in
the two tables that have "data in common" right? In both these tables,
you have data such as ANIMAL HEALTH CONSULTANT? And the PVR field in a
number field with stuff like 5.7, 4.5, 1.6, right? Ok, then, it is not
applicable to add the Class Title field from the YR2PVR table to the
query grid. The purpose of you having it there is so that you can use
it as the basis to look up the PVR value for your calculation. After
this, you can either do as before, with an unbound textbox on your form
with its control source set to
=[Under13]+[PVR]
Or, (assuming you also have the Under13 field available to the query,)
in the Field row of a blank column in the query design grid, put...
MyCalc: [Under13]+[PVR]
and then you can simply have a textbox on the form which is bound to
MyCalc (or whatever you want to call it.
 
Back
Top