IIf statment in a text box

[Guest]

I have a text box in which I have entered the following expression:

=(IIf([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",(IIf([Substandard Acts - 1]="2","Equipment not appropriate for the
job",(IIf([Substandard Acts - 1]="3","Procedures not followed by Other
Company",(IIf([Substandard Acts - 1]="4","Procedures Not Followed by F I
Canada",(IIf([Substandard Acts - 1]="5","Communication
Breakdown",(IIf([Substandard Acts - 1]="6","Operating equipment without
authority",(IIf([Substandard Acts - 1]="7","Failure to
Warn",(IIf([Substandard Acts - 1]="8","Failure to Secure",(IIf([Substandard
Acts - 1]="9","Operating at improper speed",""))))))))))))))))))

This expression works fine but the list in [Substandard Acts - 1] is a list
from 1 to 20. If I add additional IIf's to this expression I get the error
message "expression to complex". Is there a better way to do this or a way
around the error?

thanks,

safety guy

 

[M Skabialka]

Why not create a table with two fields: SubstandardActs, ResultingAction
(or some such) and use a DLOOKUP based on the number (1-20) the user enters.
That way you won't have to repeat this code anywhere either, always look it
up. Not sure why you have a - 1 in there, but the table would handle that
better.

Mich

 

[Sandra Daigle]

You really need to have all of these codes and descriptions in a table and
then pull the description directly from the table. Aside from the overly
complex nature of this statement, you also have the problem of having to do
design changes when codes/descriptions are added, modified or deleted. Since
you've indicated that the code is a list, you could just include the
description in the list and then add a calculated control that pulls its
value directly from the recordsource of the list. For example, if the
description is in the 2nd column of the list, then the following would go
into the ControlSource of your calculated control.

=[Substandard Acts - 1].[column](1)

 

[Steve Schapel]

Safety Guy,

An improvement would be the Switch() function, although I am not sure
whether there is a limit to the number of arguments, for example...
=Switch([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",[Substandard Acts - 1]="2","Equipment not appropriate for the
job",[Substandard Acts - 1]="3","Procedures not followed by Other
Company",[Substandard Acts - 1]="4","Procedures Not Followed by F I
Canada",[Substandard Acts - 1]="5","Communication
Breakdown",[Substandard Acts - 1]="6","Operating equipment without
authority",[Substandard Acts - 1]="7","Failure to Warn",[Substandard
Acts - 1]="8","Failure to Secure",[Substandard Acts - 1]="9","Operating
at improper speed")

However, the preferable approach would be to make a separate table, just
2 fields, maybe ActCode and Description, where you would list your 20
items with the "1"-"20" codes and the associated text. Then, in the
query that your form is based on, you include this table, joined to the
other main table on the ActCode field, so you can then directly include
the Description in the query, and hence onto the form.

Hope that makes sense. It is not really clear what you are trying to
achieve here, so if you need firther help with this, please post back
with further details.

By the way, as an aside, it is not a good idea to put a - as part of the
name of a field or control.

 

[Guest]

Mich,

Thanks for the reply, I think I will try the table approach as it has been
suggested by other replies!

Why not create a table with two fields: SubstandardActs, ResultingAction
(or some such) and use a DLOOKUP based on the number (1-20) the user enters.
That way you won't have to repeat this code anywhere either, always look it
up. Not sure why you have a - 1 in there, but the table would handle that
better.

Mich

I have a text box in which I have entered the following expression:

=(IIf([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",(IIf([Substandard Acts - 1]="2","Equipment not appropriate for
the
job",(IIf([Substandard Acts - 1]="3","Procedures not followed by Other
Company",(IIf([Substandard Acts - 1]="4","Procedures Not Followed by F I
Canada",(IIf([Substandard Acts - 1]="5","Communication
Breakdown",(IIf([Substandard Acts - 1]="6","Operating equipment without
authority",(IIf([Substandard Acts - 1]="7","Failure to
Warn",(IIf([Substandard Acts - 1]="8","Failure to
Secure",(IIf([Substandard
Acts - 1]="9","Operating at improper speed",""))))))))))))))))))

This expression works fine but the list in [Substandard Acts - 1] is a
list
from 1 to 20. If I add additional IIf's to this expression I get the
error
message "expression to complex". Is there a better way to do this or a
way
around the error?

thanks,

safety guy

 

[Guest]

I created a table called [Substandard Acts] and it includes two fields
[Substandard Act Number] and [Substandard Act]

I have a form called [Incident Report Cause Info Form] on which I have a
field [Substandard Acts - 1] I have then created an unbound text field
[Substandard Act] and tried to enter a DLOOKUP expression but have been
unsuccessfull!

Could you show me how to set up the DLOOKUP expression!!

safety guy

Mich,

Thanks for the reply, I think I will try the table approach as it has been
suggested by other replies!

Why not create a table with two fields: SubstandardActs, ResultingAction
(or some such) and use a DLOOKUP based on the number (1-20) the user enters.
That way you won't have to repeat this code anywhere either, always look it
up. Not sure why you have a - 1 in there, but the table would handle that
better.

Mich

I have a text box in which I have entered the following expression:

=(IIf([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",(IIf([Substandard Acts - 1]="2","Equipment not appropriate for
the
job",(IIf([Substandard Acts - 1]="3","Procedures not followed by Other
Company",(IIf([Substandard Acts - 1]="4","Procedures Not Followed by F I
Canada",(IIf([Substandard Acts - 1]="5","Communication
Breakdown",(IIf([Substandard Acts - 1]="6","Operating equipment without
authority",(IIf([Substandard Acts - 1]="7","Failure to
Warn",(IIf([Substandard Acts - 1]="8","Failure to
Secure",(IIf([Substandard
Acts - 1]="9","Operating at improper speed",""))))))))))))))))))

This expression works fine but the list in [Substandard Acts - 1] is a
list
from 1 to 20. If I add additional IIf's to this expression I get the
error
message "expression to complex". Is there a better way to do this or a
way
around the error?

thanks,

safety guy

 

[M Skabialka]

I think the field on your form which you are using as a lookup should be
[Substandard Act Number], not [Substandard Act]

e.g using 5 and "Communication Breakdown"
[Substandard Acts - 1] = DLookup("[Substandard Act]", "Substandard Acts",
"[Substandard Act]=" & [Forms]![Incident Report Cause Info
Form]![Substandard Act Number])

[Substandard Acts - 1] = DLookup("[Substandard Act]", "Substandard Acts",
"[Substandard Act]=5")

[Substandard Acts - 1] = "Communication Breakdown"

HTH
Mich
I am curious about the use of a field named [Substandard Acts - 1] which
almost seems like a calculation???

I created a table called [Substandard Acts] and it includes two fields
[Substandard Act Number] and [Substandard Act]

I have a form called [Incident Report Cause Info Form] on which I have a
field [Substandard Acts - 1] I have then created an unbound text field
[Substandard Act] and tried to enter a DLOOKUP expression but have been
unsuccessfull!

Could you show me how to set up the DLOOKUP expression!!

safety guy

Mich,

Thanks for the reply, I think I will try the table approach as it has
been
suggested by other replies!

Why not create a table with two fields: SubstandardActs,
ResultingAction
(or some such) and use a DLOOKUP based on the number (1-20) the user
enters.
That way you won't have to repeat this code anywhere either, always
look it
up. Not sure why you have a - 1 in there, but the table would handle
that
better.

Mich

I have a text box in which I have entered the following expression:

=(IIf([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",(IIf([Substandard Acts - 1]="2","Equipment not appropriate
for
the
job",(IIf([Substandard Acts - 1]="3","Procedures not followed by
Other
Company",(IIf([Substandard Acts - 1]="4","Procedures Not Followed by
F I
Canada",(IIf([Substandard Acts - 1]="5","Communication
Breakdown",(IIf([Substandard Acts - 1]="6","Operating equipment
without
authority",(IIf([Substandard Acts - 1]="7","Failure to
Warn",(IIf([Substandard Acts - 1]="8","Failure to
Secure",(IIf([Substandard
Acts - 1]="9","Operating at improper speed",""))))))))))))))))))

This expression works fine but the list in [Substandard Acts - 1] is
a
list
from 1 to 20. If I add additional IIf's to this expression I get the
error
message "expression to complex". Is there a better way to do this or
a
way
around the error?

thanks,

safety guy

 

[Guest]

Try this:

=DLookup("Substandard Act", "Substandard Acts", "[Substandard Act Number] =
" & [Substandard Acts - 1])

Mauricio Silva

I created a table called [Substandard Acts] and it includes two fields
[Substandard Act Number] and [Substandard Act]

I have a form called [Incident Report Cause Info Form] on which I have a
field [Substandard Acts - 1] I have then created an unbound text field
[Substandard Act] and tried to enter a DLOOKUP expression but have been
unsuccessfull!

Could you show me how to set up the DLOOKUP expression!!

safety guy

Mich,

Thanks for the reply, I think I will try the table approach as it has been
suggested by other replies!

Why not create a table with two fields: SubstandardActs, ResultingAction
(or some such) and use a DLOOKUP based on the number (1-20) the user enters.
That way you won't have to repeat this code anywhere either, always look it
up. Not sure why you have a - 1 in there, but the table would handle that
better.

Mich

I have a text box in which I have entered the following expression:

=(IIf([Substandard Acts - 1]="1","Control review/Pre-Job planning
deficient",(IIf([Substandard Acts - 1]="2","Equipment not appropriate for
the
job",(IIf([Substandard Acts - 1]="3","Procedures not followed by Other
Company",(IIf([Substandard Acts - 1]="4","Procedures Not Followed by F I
Canada",(IIf([Substandard Acts - 1]="5","Communication
Breakdown",(IIf([Substandard Acts - 1]="6","Operating equipment without
authority",(IIf([Substandard Acts - 1]="7","Failure to
Warn",(IIf([Substandard Acts - 1]="8","Failure to
Secure",(IIf([Substandard
Acts - 1]="9","Operating at improper speed",""))))))))))))))))))

This expression works fine but the list in [Substandard Acts - 1] is a
list
from 1 to 20. If I add additional IIf's to this expression I get the
error
message "expression to complex". Is there a better way to do this or a
way
around the error?

thanks,

safety guy

 

[Steve Schapel]

Safety Guy,

Mauricio's suggestuion is correct, assuming [Substandard Acts - 1] is a
number field. But it would appear from your earlier post that it is
probably actually a text data type, in which case you would use...

=DLookup("[Substandard Act]", "Substandard Acts", "[Substandard Act
Number]='" & [Substandard Acts - 1] & "'")

 

[Guest]

Steve/Mauricio/Mich,

Sorry about the - 1 stuff, as you can see in the expression it is no longer
there, the tables field name is [Substandard Acts -1] but when I created the
drop down box for picking the numbers 1 - 20 I called it [Substandard Act 1]

I have played with the expression and came up with the following expression
that appears to work except that when I change the [Substandard Act 1] field
the field that I have this expression entered in [Text80] does not refresh
automatically, I have to close the form and re-open it to make the field
refresh itself.

=DLookUp("[Substandard Act]","[Substandard Acts]","[Substandard Act Number]
= Forms![Incident Report Cause Info Form]![Substandard Act 1]")

Is there a setting in the [Text80] field I am missing or how can I get it to
refresh if the user changes the [Substandard Act 1] field?

safety guy

Safety Guy,

Mauricio's suggestuion is correct, assuming [Substandard Acts - 1] is a
number field. But it would appear from your earlier post that it is
probably actually a text data type, in which case you would use...

=DLookup("[Substandard Act]", "Substandard Acts", "[Substandard Act
Number]='" & [Substandard Acts - 1] & "'")

--
Steve Schapel, Microsoft Access MVP

Try this:

=DLookup("Substandard Act", "Substandard Acts", "[Substandard Act Number] =
" & [Substandard Acts - 1])

Mauricio Silva

 

[Steve Schapel]

Safety Guy,

What was wrong with the expression syntax that Mauricio and I suggested?

Anyway, now that I know you are using a combobox to enter the number
1-20, it is a lot easier and more efficient to include the Substandard
Act as a column in the combobox's Row Source, and then use an expression
like this in the Control Source of Text80...
=[Substandard Act 1].[Column](1)

 

[Steve Schapel]

Ok, and now I notice that this is similar to the idea suggested by
Sandra Daigle.