Expression You Enter is Too Complex - Help PLEASE

A

ASSK

Hi,

Below is my "iif" expression in access form. Basically I have a field where
I want the result to automatically populate depending what you choose from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given Occurrence]="Insignificant","2 &
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 & 3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "Catastrophic","6 & 7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]= "minor","4 & 5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "moderate","4 & 5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6 & 7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "catastrophic","6 &
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a text box to
bring the results back but when I copy this "iif" string into the form, it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
 
A

Arvin Meyer [MVP]

You might try using a Select Case statement in VBA. This would simplify is
tremendously. Being a new user, you are probably unfamiliar with VBA coding,
so I'll point out an example:

http://www.datastrat.com/Code/LockIt.txt

Now in each of the controls which will affect the outcome, (likelihood and
Loss Given Occurrence) check for the other to be filled, like:

Sub Loss_Given_Occurrence_AfterUpdate()
If Len([likelihood] & vbNullString) > 0 Then
' Run your sub by using its name
Else
MsgBox "Please fill in Loss Given Occurence", vbOKOnly
End If
End Sub
 
A

Allen Browne

Seems like you you have 2 fields (Likelihood and [Loss Given Occurrence]),
and based on those 2 values you want to assign 2 other numeric results.

To set this up correctly, I would suggest the following:
1. Create a table, with 1 text field to hold the possible values for
Likelihood. Enter 3 records (for rare, unlikely, and possible.)

2. Create a table with 1 text field to hold the possible values for [Loss
Given Occurrence]. Enter the 5 records.

You can use these 2 tables as the RowSource of combo boxes on your form.

3. Create a table that contains the results for the combinations. Fields:
Likelihood Text
Loss Given Occurrence Text
Result1 Number
Result2 Number
The combination of the first 2 fields will be the primary key. Enter a
record for each combination, e.g.
rare insignificant 2 3
rare minor 2 3
rare moderate 4 5

Now you can create a query using your existing table and Table3 above. This
query will give you the 2 result values.

Alternatively, you can now look up the result values to display in your
form. For example the first text box might have this as ControlSource:
=DLookup("Result1", "Table3", "([likelihood = """ & [likelilhood] &
""") AND ([Loss Given Occurrence] = """ &
[Loss Given Occurrence] & """)"

I have assumed:
a) The 2 results you want to assign need to treated as different values.
b) These 2 results should *always* match the combination of Likelihood +
[Loss Given Occurrence], i.e. there is never a case where the results might
be manually changed to something other than what the lookup table contains.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ASSK said:
Below is my "iif" expression in access form. Basically I have a field
where
I want the result to automatically populate depending what you choose from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given Occurrence]="Insignificant","2 &
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 & 3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "Catastrophic","6 &
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]= "minor","4 &
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "moderate","4 &
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6 & 7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "catastrophic","6
&
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a text box
to
bring the results back but when I copy this "iif" string into the form, it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
Click to expand...
 
A

ASSK

Thank you for your help.

What i've dont is created a table called "Likelihood", a table "Loss of
Occurrence" and a third table as you mentioned a text field "likelihood",
"loss of occurrence" and a field call result "e.g. 2 & 3) etc.

So I now have 3 tables, I don't understand what you mean by the
Dlookup...Please explain how I can do this in form.

Allen Browne said:
Seems like you you have 2 fields (Likelihood and [Loss Given Occurrence]),
and based on those 2 values you want to assign 2 other numeric results.

To set this up correctly, I would suggest the following:
1. Create a table, with 1 text field to hold the possible values for
Likelihood. Enter 3 records (for rare, unlikely, and possible.)

2. Create a table with 1 text field to hold the possible values for [Loss
Given Occurrence]. Enter the 5 records.

You can use these 2 tables as the RowSource of combo boxes on your form.

3. Create a table that contains the results for the combinations. Fields:
Likelihood Text
Loss Given Occurrence Text
Result1 Number
Result2 Number
The combination of the first 2 fields will be the primary key. Enter a
record for each combination, e.g.
rare insignificant 2 3
rare minor 2 3
rare moderate 4 5

Now you can create a query using your existing table and Table3 above. This
query will give you the 2 result values.

Alternatively, you can now look up the result values to display in your
form. For example the first text box might have this as ControlSource:
=DLookup("Result1", "Table3", "([likelihood = """ & [likelilhood] &
""") AND ([Loss Given Occurrence] = """ &
[Loss Given Occurrence] & """)"

I have assumed:
a) The 2 results you want to assign need to treated as different values.
b) These 2 results should *always* match the combination of Likelihood +
[Loss Given Occurrence], i.e. there is never a case where the results might
be manually changed to something other than what the lookup table contains.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ASSK said:
Below is my "iif" expression in access form. Basically I have a field
where
I want the result to automatically populate depending what you choose from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given Occurrence]="Insignificant","2 &
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 & 3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "Catastrophic","6 &
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]= "minor","4 &
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "moderate","4 &
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6 & 7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "catastrophic","6
&
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a text box
to
bring the results back but when I copy this "iif" string into the form, it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
Click to expand...
Click to expand...
 
A

Allen Browne

The expression goes into the ControlSource property of a text box.

Here's some help on how to build a DLookup() expression:
http://allenbrowne.com/casu-07.html

Here's some broader information on how to handle calculated fields in
general:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ASSK said:
Thank you for your help.

What i've dont is created a table called "Likelihood", a table "Loss of
Occurrence" and a third table as you mentioned a text field "likelihood",
"loss of occurrence" and a field call result "e.g. 2 & 3) etc.

So I now have 3 tables, I don't understand what you mean by the
Dlookup...Please explain how I can do this in form.

Allen Browne said:
Seems like you you have 2 fields (Likelihood and [Loss Given
Occurrence]),
and based on those 2 values you want to assign 2 other numeric results.

To set this up correctly, I would suggest the following:
1. Create a table, with 1 text field to hold the possible values for
Likelihood. Enter 3 records (for rare, unlikely, and possible.)

2. Create a table with 1 text field to hold the possible values for [Loss
Given Occurrence]. Enter the 5 records.

You can use these 2 tables as the RowSource of combo boxes on your form.

3. Create a table that contains the results for the combinations. Fields:
Likelihood Text
Loss Given Occurrence Text
Result1 Number
Result2 Number
The combination of the first 2 fields will be the primary key. Enter a
record for each combination, e.g.
rare insignificant 2 3
rare minor 2 3
rare moderate 4 5

Now you can create a query using your existing table and Table3 above.
This
query will give you the 2 result values.

Alternatively, you can now look up the result values to display in your
form. For example the first text box might have this as ControlSource:
=DLookup("Result1", "Table3", "([likelihood = """ & [likelilhood] &
""") AND ([Loss Given Occurrence] = """ &
[Loss Given Occurrence] & """)"

I have assumed:
a) The 2 results you want to assign need to treated as different values.
b) These 2 results should *always* match the combination of Likelihood +
[Loss Given Occurrence], i.e. there is never a case where the results
might
be manually changed to something other than what the lookup table
contains.

ASSK said:
Below is my "iif" expression in access form. Basically I have a field
where
I want the result to automatically populate depending what you choose
from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given Occurrence]="Insignificant","2
&
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 &
3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 &
5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "Catastrophic","6
&
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]= "minor","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "moderate","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6 &
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"catastrophic","6
&
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a text
box
to
bring the results back but when I copy this "iif" string into the form,
it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
Click to expand...
Click to expand...
Click to expand...
 
A

ASSK

This is the Dlookup code that I have, but when I choose combo box for
likelihood and Loss of Occurrence, it's not returning the rersults from the
table:

=DLookUp("Result","Results Combination",([likelihood]="" & [likelihood] &
"'") And ([Loss Given Occurrence]="'" & [Loss Given Occurrence] & "'"))

Why is that?

Allen Browne said:
The expression goes into the ControlSource property of a text box.

Here's some help on how to build a DLookup() expression:
http://allenbrowne.com/casu-07.html

Here's some broader information on how to handle calculated fields in
general:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ASSK said:
Thank you for your help.

What i've dont is created a table called "Likelihood", a table "Loss of
Occurrence" and a third table as you mentioned a text field "likelihood",
"loss of occurrence" and a field call result "e.g. 2 & 3) etc.

So I now have 3 tables, I don't understand what you mean by the
Dlookup...Please explain how I can do this in form.

Allen Browne said:
Seems like you you have 2 fields (Likelihood and [Loss Given
Occurrence]),
and based on those 2 values you want to assign 2 other numeric results.

To set this up correctly, I would suggest the following:
1. Create a table, with 1 text field to hold the possible values for
Likelihood. Enter 3 records (for rare, unlikely, and possible.)

2. Create a table with 1 text field to hold the possible values for [Loss
Given Occurrence]. Enter the 5 records.

You can use these 2 tables as the RowSource of combo boxes on your form.

3. Create a table that contains the results for the combinations. Fields:
Likelihood Text
Loss Given Occurrence Text
Result1 Number
Result2 Number
The combination of the first 2 fields will be the primary key. Enter a
record for each combination, e.g.
rare insignificant 2 3
rare minor 2 3
rare moderate 4 5

Now you can create a query using your existing table and Table3 above.
This
query will give you the 2 result values.

Alternatively, you can now look up the result values to display in your
form. For example the first text box might have this as ControlSource:
=DLookup("Result1", "Table3", "([likelihood = """ & [likelilhood] &
""") AND ([Loss Given Occurrence] = """ &
[Loss Given Occurrence] & """)"

I have assumed:
a) The 2 results you want to assign need to treated as different values.
b) These 2 results should *always* match the combination of Likelihood +
[Loss Given Occurrence], i.e. there is never a case where the results
might
be manually changed to something other than what the lookup table
contains.


Below is my "iif" expression in access form. Basically I have a field
where
I want the result to automatically populate depending what you choose
from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given Occurrence]="Insignificant","2
&
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 &
3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 &
5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 & 5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "Catastrophic","6
&
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]= "minor","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "moderate","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6 &
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"catastrophic","6
&
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a text
box
to
bring the results back but when I copy this "iif" string into the form,
it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Natually we can't see how your tables are set up.

Perhaps the combo is bound to a number and not to the text?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ASSK said:
This is the Dlookup code that I have, but when I choose combo box for
likelihood and Loss of Occurrence, it's not returning the rersults from
the
table:

=DLookUp("Result","Results Combination",([likelihood]="" & [likelihood] &
"'") And ([Loss Given Occurrence]="'" & [Loss Given Occurrence] & "'"))

Why is that?

Allen Browne said:
The expression goes into the ControlSource property of a text box.

Here's some help on how to build a DLookup() expression:
http://allenbrowne.com/casu-07.html

Here's some broader information on how to handle calculated fields in
general:
http://allenbrowne.com/casu-14.html

ASSK said:
Thank you for your help.

What i've dont is created a table called "Likelihood", a table "Loss of
Occurrence" and a third table as you mentioned a text field
"likelihood",
"loss of occurrence" and a field call result "e.g. 2 & 3) etc.

So I now have 3 tables, I don't understand what you mean by the
Dlookup...Please explain how I can do this in form.

:

Seems like you you have 2 fields (Likelihood and [Loss Given
Occurrence]),
and based on those 2 values you want to assign 2 other numeric
results.

To set this up correctly, I would suggest the following:
1. Create a table, with 1 text field to hold the possible values for
Likelihood. Enter 3 records (for rare, unlikely, and possible.)

2. Create a table with 1 text field to hold the possible values for
[Loss
Given Occurrence]. Enter the 5 records.

You can use these 2 tables as the RowSource of combo boxes on your
form.

3. Create a table that contains the results for the combinations.
Fields:
Likelihood Text
Loss Given Occurrence Text
Result1 Number
Result2 Number
The combination of the first 2 fields will be the primary key. Enter a
record for each combination, e.g.
rare insignificant 2 3
rare minor 2 3
rare moderate 4 5

Now you can create a query using your existing table and Table3 above.
This
query will give you the 2 result values.

Alternatively, you can now look up the result values to display in
your
form. For example the first text box might have this as ControlSource:
=DLookup("Result1", "Table3", "([likelihood = """ & [likelilhood]
&
""") AND ([Loss Given Occurrence] = """ &
[Loss Given Occurrence] & """)"

I have assumed:
a) The 2 results you want to assign need to treated as different
values.
b) These 2 results should *always* match the combination of Likelihood
+
[Loss Given Occurrence], i.e. there is never a case where the results
might
be manually changed to something other than what the lookup table
contains.


Below is my "iif" expression in access form. Basically I have a
field
where
I want the result to automatically populate depending what you
choose
from
the other two fields:

=IIf([likelihood]="rare" And [Loss Given
Occurrence]="Insignificant","2
&
3",iif([likelihood]="rare" and [Loss Given Occurrence]= "minor","2 &
3",
iif([likelihood]="rare" and [Loss Given Occurrence]= "moderate","4 &
5",
iif([likelihood]="rare" and [Loss Given Occurrence]= "major","4 &
5",
iif([likelihood]="rare" and [Loss Given Occurrence]=
"Catastrophic","6
&
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"insignificant","2 &
3", iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"minor","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"moderate","4
&
5",
iif([likelihood]="unlikely" and [Loss Given Occurrence]= "major","6
&
7",
iif([likelihood]="unlikely" and [Loss Given Occurrence]=
"catastrophic","6
&
7", iif([likelihood]="Possible" and [Loss Given Occurrence]=
"insignificant","4 & 5", iif([likelihood]="Possible" and [Loss Given
Occurrence]= "minor","4 & 5", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "moderate","6 & 7", iif([likelihood]="Possible" and
[Loss
Given
Occurrence]= "major","6 & 7", iif([likelihood]="Possible" and [Loss
Given
Occurrence]= "catastrophic","8 & 10","" )))))))))))))))

Can someone please help me how I can do this in a Form? I have a
text
box
to
bring the results back but when I copy this "iif" string into the
form,
it
come back as your expression is too complex.

PLEASE help. QUITE DESPERATE..I am a new user too.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

colour cells using VBA 2
Please Help! The Expression you entered is too complex 0
3 text lookup tables use to define contents of a cell 3
Complicated Like Expression 3
Code expression for multiple combinations 4
email problems 9
Need help setting query criteria from a form 3
Summing occurrences within a list 2

Top