Solved the expression you entered is too complex (1 Viewer)

[TipsyWolf]

hey guys,
i face with another problem.
in my countinius form i have riskcode textbox bound to Tmain!Riskcode. Its numeric only from 1 to 25. every code has sovernity \ likehood code (identifier)
for example
riskcode 1 = 1A
....
riskcode 7 = 3A
....

i put unbound textbox that should display this identifier. in control source i put

=IIf([riskcode]=1;"1A";IIf([riskcode]=2;"1B";IIf([riskcode]=3;"1C";IIf([riskcode]=4;"2A";IIf([riskcode]=5;"2B";IIf([riskcode]=6;"2C";IIf([riskcode]=7;"3A";IIf([riskcode]=8;"1D";IIf([riskcode]=10;"1E";IIf([riskcode]=11;"2D";IIf([riskcode]=12;"3B";IIf([riskcode]=13;"3C";IIf([riskcode]=14;"4A";IIf([riskcode]=15;"2E";IIf([riskcode]=16;"3D";IIf([riskcode]=17;"4B";IIf([riskcode]=18;"5A";IIf([riskcode]=19;"5B";IIf([riskcode]=20;"3E";IIf([riskcode]=21;"4D";IIf([riskcode]=22;"4E";IIf([riskcode]=23;"5C";IIf([riskcode]=24;"5D";IIf([riskcode]=25;"5E"))))))))))))))))))))))))

and it says the expression you entered is too complex.
any solutions \ advices for this ?

maybe vba ? if there is an option, please help me with just few lines and the rest i will do it myself. i know - its too much time consuming and i can't push u to it. u guys already helped me a lot !

 

[cheekybuddha]

When you have this many it's easier to create a table for risk codes; then join the table without the need for such an unwieldy expression or VBA function.

 

[TipsyWolf]

When you have this many it's easier to create a table for risk codes; then join the table without the need for such an unwieldy expression or VBA function.

hmm.. yeah, might be the case. thank you.

 

[GPGeorge]

When you have this many it's easier to create a table for risk codes; then join the table without the need for such an unwieldy expression or VBA function.

Lookup tables are cool.

 

[Gasman]

Whilst I would also go for a lookup table, just in cas ethe data changes, could a Switch() function be used here as well? if it did not change?

 

[arnelgp]

table, tblRiskCode:

RiskCode	Sovernity
1​	1A
2​	1B
3​	1C
4​	2A
5​	2B
6​	2C
7​	3A
8​	3B
9​	3C
10​	4A
11​	4B
12​	4C
13​	5A
14​	5B
15​	5C
16​	6A
17​	6B
18​	6C
19​	7A
20​	7B
21​	7C
22​	8A
23​	8B
24​	8C
25​	9A

then create a query and use it as Recordsource of your form:

Select yourTable.*, tblRiskCode.Sovernity From yourTable Left Join tblRiskCode On yourTable.RiskCode = tblRiskCode.RiskCode;

 

[cheekybuddha]

Whilst I would also go for a lookup table, just in cas ethe data changes, could a Switch() function be used here as well? if it did not change?

There are 25 risk codes - with either Switch() or nested IIf(), my guess is that you are butting up against or beyond the maximum number of arguments.

 

[The_Doc_Man]

Probably too much depth in the nested IIF. That certainly looks like more than twenty nests of the IIF FALSE option. I see three options, perhaps.

First, look at the SWITCH function.

Switch function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

The IIF method NESTS the alternative values but SWITCH merely LINEARIZES them. David's concern about limits on the SWITCH function are also valid, and therefore I can't guarantee you wouldn't blow out that option either - but I think the "too complex" error is about nesting depth, not necessarily the length of the expression string.

=SWITCH( [riskcode]=1, "1A", [riskcode]=2,"1B", [riskcode]=3,"1C", [riskcode]=4,"2A", [riskcode]=5,"2B", ...

Second, this is a textbox control source so it is on a form. It seems to relate to a value in something called [riskcode] so if that comes from a recordsource, that means the form is at least partly bound. IF this is a partially bound form and the textbox just happens to be unbound, then that implies there will be a usable Current event. If so, then you can put a SELECT CASE statement in that Current event.

Private Sub Form_Current
...
SELECT CASE [riskcode]
  CASE 1
    unboundtextbox = "1A"
  CASE 2
    unboundtextbox = "1B"
....
  CASE ELSE
    unboundtextbox = "?"
END SELECT
...
End Sub

And then David's and Arnel's suggestion that you have a table that lists the numeric and text equivalents. If the numeric field is RCID and the text equivalent is RCCode in table RiskTrnslate, you would have

=DLookup( "[RCCode]", "RiskTrnslate", "[RCID]=" & [riskcode] )

There are more options but these are the three simplest ones.

EDIT - It appears that both David and Arnel posted while I was composing my own reply.

 

[cheekybuddha]

Choose() is probably a better choice than Switch() if you want to avoid the table option.

 

[CJ_London]

and it says the expression you entered is too complex.

that error usually implies a problem with your data - typically a null value.

As written, your nested iif function will fail if you have a null riskcode in your data

 

[TipsyWolf]

that error usually implies a problem with your data - typically a null value.

As written, your nested iif function will fail if you have a null riskcode in your data

i have 4 records and everyone of them has riskcode value.
i already faced with this problem a month ago , when i put long code to control source. access doesn't like it.

 

[Gasman]

Why not just use the Sovernity directly?

 

[TipsyWolf]

Why not just use the Sovernity directly?

i have 25 bottons and user should just click on one of these while registring new risk.

and I made info from buttons goes to Tmain!RiskCode , but not with Sivernity Likehood ID (like 1A or 2D) but numbers from 1 to 25. why ?
cause i knew i will need tons of calculations later like
IIF( bla bla bla between 1 and 7 or 20 and 25) which agree with me - its much easier then 1A and 1B, etc...
i have also dbl click events with tons of conditinos where i need to specify risk level
and also when i create a query its much easier to set rnage of numbers , than 1A and 1B and and and etc...

i made numbers goes like this

so riskcode between 1 and 7 = green risk level or 1A,1B, 1C, 2A,2B, 2C and 3A
riskcode between 14 and 19 = orange risk level .. .etc

 

[TipsyWolf]

and i have almost finished db (just polishing before it goes live)

and there are tons of dcount and dbl click events. every cells has them

 

[TipsyWolf]

damn, and i just love my work. and yours as well as u guys helped me a lot with my project

 

[TipsyWolf]

SELECT CASE

i have an error i guess.
not sure what i did wrong

Private Sub Form_Current()
Select Case [RiskCode]
    Case 1: txtb = "1A"
    Case 2: txtb = "1B"
    Case 3: txtb = "1C"
    Case 4: txtb = "2A"
    Case 5: txtb = "2B"
    Case 6: txtb = "2C"
    Case 7: txtb = "3A"
    Case 8: txtb = "1D"
    Case 9: txtb = "1E"
    Case 10: txtb = "2D"
    Case 11: txtb = "3B"
    Case 12: txtb = "3C"
    Case 13: txtb = "4A"
    Case 14: txtb = "2E"
    Case 15: txtb = "3D"
    Case 16: txtb = "4B"
    Case 17: txtb = "4C"
    Case 18: txtb = "5A"
    Case 19: txtb = "5B"
    Case 20: txtb = "3E"
    Case 21: txtb = "4D"
    Case 22: txtb = "4E"
    Case 23: txtb = "5C"
    Case 24: txtb = "5D"
    Case 25: txtb = "5E"
    Case Else txtb = "recheck"
   
  End Select

End Sub

 

[nector]

If you are using those codes within access and the DB or BE is access then its fine, but if you want to use the SQL server with Access then expect to pay a heavy price in terms of slowness.

 

[TipsyWolf]

SQL server

i dont know even what that it .. so
my db will be located in local storage and up to 50 ppl will be using it via local network. 1-2 might be simonteniulsly.

 

[Gasman]

i have an error i guess.
not sure what i did wrong

Private Sub Form_Current()
Select Case [RiskCode]
    Case 1: txtb = "1A"
    Case 2: txtb = "1B"
    Case 3: txtb = "1C"
    Case 4: txtb = "2A"
    Case 5: txtb = "2B"
    Case 6: txtb = "2C"
    Case 7: txtb = "3A"
    Case 8: txtb = "1D"
    Case 9: txtb = "1E"
    Case 10: txtb = "2D"
    Case 11: txtb = "3B"
    Case 12: txtb = "3C"
    Case 13: txtb = "4A"
    Case 14: txtb = "2E"
    Case 15: txtb = "3D"
    Case 16: txtb = "4B"
    Case 17: txtb = "4C"
    Case 18: txtb = "5A"
    Case 19: txtb = "5B"
    Case 20: txtb = "3E"
    Case 21: txtb = "4D"
    Case 22: txtb = "4E"
    Case 23: txtb = "5C"
    Case 24: txtb = "5D"
    Case 25: txtb = "5E"
    Case Else txtb = "recheck"
  
  End Select

End Sub

View attachment 113647

You do not say what the error is?

I would be using Me.txtb if that is a control on the form.

 

[Pat Hartman]

and there are tons of dcount and dbl click events. every cells has them

Your language says it all You have created a spreadsheet rather than a normalized relational database. Relational databases do not have "cells". Each "cell" in a spreadsheet is an independent entity and that is why you have all those domain functions, each of which is running a separate query.