"Expression too Complex"

R

Robert McClain

I'm using Microsoft Access 2002 and have a query that
looks at field Q33_INSURANCE and populates the new field
(Health Insurance Provider) based on the data within
Q33_INSURANCE. I knew that there might be a limitation on
the complexity of the expression, but was wondering if
there is another way to go about this. Below if the
expression that I'm using in the query:


Health Insurance Provider: IIf([Q33_INSURANCE]
="01","None - Do not have health insurance",IIf
([Q33_INSURANCE]="02","Do not know",IIf([Q33_INSURANCE]
="04","Access",IIf([Q33_INSURANCE]="05","Aetna/US
Healthcare",IIf([Q33_INSURANCE]="06","Gateway",IIf
([Q33_INSURANCE]="07","Health America/Health
Assurance",IIf([Q33_INSURANCE]="08","Highmark/Blue
Cross/Blue Shield",IIf([Q33_INSURANCE]="09","Keystone",IIf
([Q33_INSURANCE]="10", "Medicare/Medicaid",IIf
([Q33_INSURANCE]="11", "Self",IIf([Q33_INSURANCE]
="12", "UPMC",IIf([Q33_INSURANCE]="13", "VA", IIf
([Q33_INSURANCE]="14", "HMO", IIf([Q33_INSURANCE]
="15", "Commercial", IIf([Q33_INSURANCE]
="16", "Other", "Invalid Health Insurance Provider
Code"))))))))))))


Thanks for any help in advance!
 
G

Guest

I think MS Access is limited to 7 nested IIFs. I have run
into the same problem and have had to learn to write
Public Functions in VBA and then call them back into the
query. There may be a better way that someone else can
suggest without you having to use VBA or SQL, but this was
the way I went.

Someone on this same forum helped me change from using the
IIF() to a Select Case Methodology. For instance, here is
an example that was converted from a multiple nested IIF
statement to a Select Case VBA statement:

Public Function LTVrtg1 _
(PropType As String, _
LTVrtgHO As Double, _
ImpliedLTV As Double) As Double

If PropType = "HO" Then
LTVrtg1 = LTVrtgHO
Else
Select Case ImpliedLTV
Case Is < 0.7
LTVrtg1 = 4
Case Is < 0.75
LTVrtg1 = 3
Case Is < 0.8
LTVrtg1 = 2
Case Is >= 0.8
LTVrtg1 = 1
Case Else
LTVrtg1 = 0
End Select
End If
End Function

You can substitute you criteria in and rename it. I'm
sure someone more experienced can provide a more elegant
and efficient solution that we can all learn from, but
this is my 2 cents. Hope it helps!

Ken
 
S

Steve Schapel

Robert,

There is a limit to the number of nested IIf expressions allowed in a
query, I think it's 7. I think you could use the Switch function
instead, e.g.
Health Insurance Provider: Switch([Q33_INSURANCE]="01","None - Do not
have health insurance",[Q33_INSURANCE]="02","Do not
know",[Q33_INSURANCE]="04","Access",[Q33_INSURANCE]="05","Aetna/US
Healthcare" ... "Other")

However, there is a much, much easier approach. Just make a simple
table, if you haven't already got such, with ProviderCode and
ProviderDescription (or whatever field names suit). Then, you just add
this table to your query, join to the Q33_Insurance field in your main
table from the ProviderCode field in the lookup table, and simply use
the ProviderDescription directly in your query, forms, reports, etc.
 
G

Guest

Steve,

Thanks for your help... I tried both options: Using the
SWITCH function, I get the same error message "Expression
Too Complex".

You second choice will work, except for any code that is
not in the table, I want it to say "Invalid Code". Any
suggestions?

Thanks,
Robert McClain
-----Original Message-----
Robert,

There is a limit to the number of nested IIf expressions allowed in a
query, I think it's 7. I think you could use the Switch function
instead, e.g.
Health Insurance Provider: Switch([Q33_INSURANCE] ="01","None - Do not
have health insurance",[Q33_INSURANCE]="02","Do not
know",[Q33_INSURANCE]="04","Access",[Q33_INSURANCE] ="05","Aetna/US
Healthcare" ... "Other")

However, there is a much, much easier approach. Just make a simple
table, if you haven't already got such, with ProviderCode and
ProviderDescription (or whatever field names suit). Then, you just add
this table to your query, join to the Q33_Insurance field in your main
table from the ProviderCode field in the lookup table, and simply use
the ProviderDescription directly in your query, forms, reports, etc.

--
Steve Schapel, Microsoft Access MVP


Robert said:
I'm using Microsoft Access 2002 and have a query that
looks at field Q33_INSURANCE and populates the new field
(Health Insurance Provider) based on the data within
Q33_INSURANCE. I knew that there might be a limitation on
the complexity of the expression, but was wondering if
there is another way to go about this. Below if the
expression that I'm using in the query:


Health Insurance Provider: IIf([Q33_INSURANCE]
="01","None - Do not have health insurance",IIf
([Q33_INSURANCE]="02","Do not know",IIf([Q33_INSURANCE]
="04","Access",IIf([Q33_INSURANCE]="05","Aetna/US
Healthcare",IIf([Q33_INSURANCE]="06","Gateway",IIf
([Q33_INSURANCE]="07","Health America/Health
Assurance",IIf([Q33_INSURANCE]="08","Highmark/Blue
Cross/Blue Shield",IIf([Q33_INSURANCE] ="09","Keystone",IIf
([Q33_INSURANCE]="10", "Medicare/Medicaid",IIf
([Q33_INSURANCE]="11", "Self",IIf([Q33_INSURANCE]
="12", "UPMC",IIf([Q33_INSURANCE]="13", "VA", IIf
([Q33_INSURANCE]="14", "HMO", IIf([Q33_INSURANCE]
="15", "Commercial", IIf([Q33_INSURANCE]
="16", "Other", "Invalid Health Insurance Provider
Code"))))))))))))


Thanks for any help in advance!
Click to expand...
.
Click to expand...
 
S

Steve Schapel

Robert,

In your query, use a Left Join between the tables, i.e. in query design
double-click on the join line and select the option that says something
like 'all records from main table and related records from lookup
table'. And then, in the query, instead of the ProviderDescription
directly, make a calculated field like this...
ProvDescExt: Nz([ProviderDescription],"Invalid code")
 
R

Robert McClain

Thanks! It works great!

-----Original Message-----
Robert,

In your query, use a Left Join between the tables, i.e. in query design
double-click on the join line and select the option that says something
like 'all records from main table and related records from lookup
table'. And then, in the query, instead of the ProviderDescription
directly, make a calculated field like this...
ProvDescExt: Nz([ProviderDescription],"Invalid code")

--
Steve Schapel, Microsoft Access MVP


[email protected] said:
Steve,

Thanks for your help... I tried both options: Using the
SWITCH function, I get the same error message "Expression
Too Complex".

You second choice will work, except for any code that is
not in the table, I want it to say "Invalid Code". Any
suggestions?

Thanks,
Robert McClain
Click to expand...
.
Click to expand...
 
D

Duane Hookom

I wouldn't procede any further without creating a lookup table of the
[Q33_Insurance] field and the company name. This would allow you to add more
numbers and companies without having to maintain expressions or code. Adding
"16" "Nopayum Co." in the table would not require any object changes.
 
K

kbath

wrote:
*I think MS Access is limited to 7 nested IIFs. I have run
into the same problem and have had to learn to write
Public Functions in VBA and then call them back into the
query. There may be a better way that someone else can
suggest without you having to use VBA or SQL, but this was
the way I went.

Someone on this same forum helped me change from using the
IIF() to a Select Case Methodology. For instance, here is
an example that was converted from a multiple nested IIF
statement to a Select Case VBA statement:

Public Function LTVrtg1 _
(PropType As String, _
LTVrtgHO As Double, _
ImpliedLTV As Double) As Double

If PropType = "HO" Then
LTVrtg1 = LTVrtgHO
Else
Select Case ImpliedLTV
Case Is < 0.7
LTVrtg1 = 4
Case Is < 0.75
LTVrtg1 = 3
Case Is < 0.8
LTVrtg1 = 2
Case Is >= 0.8
LTVrtg1 = 1
Case Else
LTVrtg1 = 0
End Select
End If
End Function

You can substitute you criteria in and rename it. I'm
sure someone more experienced can provide a more elegant
and efficient solution that we can all learn from, but
this is my 2 cents. Hope it helps!

Ken
-----Original Message-----
I'm using Microsoft Access 2002 and have a query that
looks at field Q33_INSURANCE and populates the new field
(Health Insurance Provider) based on the data within
Q33_INSURANCE. I knew that there might be a limitation on
the complexity of the expression, but was wondering if
there is another way to go about this. Below if the
expression that I'm using in the query:


Health Insurance Provider: IIf([Q33_INSURANCE]
="01","None - Do not have health insurance",IIf
([Q33_INSURANCE]="02","Do not know",IIf([Q33_INSURANCE]
="04","Access",IIf([Q33_INSURANCE]="05","Aetna/US
Healthcare",IIf([Q33_INSURANCE]="06","Gateway",IIf
([Q33_INSURANCE]="07","Health America/Health
Assurance",IIf([Q33_INSURANCE]="08","Highmark/Blue
Cross/Blue Shield",IIf([Q33_INSURANCE]="09","Keystone",IIf
([Q33_INSURANCE]="10", "Medicare/Medicaid",IIf
([Q33_INSURANCE]="11", "Self",IIf([Q33_INSURANCE]
="12", "UPMC",IIf([Q33_INSURANCE]="13", "VA", IIf
([Q33_INSURANCE]="14", "HMO", IIf([Q33_INSURANCE]
="15", "Commercial", IIf([Q33_INSURANCE]
="16", "Other", "Invalid Health Insurance Provider
Code"))))))))))))


Thanks for any help in advance!
.
Click to expand...
Click to expand...


-
kbat
 
K

kbath

I am new to forums so I don't know how to put my 2 cents worth in here
But I have the identical problem to Bob and Steve says to create
simple table, etc. My problem is that I have a table with about 400
records, and a field called formTypes which allocate a particula
formtype to each record( 15 different formtypes in all), eg, formtyp
1001, 1002, 1003 etc. What I want to do is create a calculated quer
that allocates a form name to the formtypes to make the report mor
user friendly eg, if formtype - 1001, formname= Form A, if formtype
1002, formname=Form B, etc. By the time I allocate 15 differen
formtypes and formnames, I get "expression too complex". I dont kno
how creating a new table and having to do it all again will help. Ca
someone please explain it to me in terms that a 2 year old woul
understand? I'm really green at this stuff.

Steve said:
*Robert,

There is a limit to the number of nested IIf expressions allowed i
a
query, I think it's 7. I think you could use the Switch function
instead, e.g.
Health Insurance Provider: Switch([Q33_INSURANCE]="01","None - D
not
have health insurance",[Q33_INSURANCE]="02","Do not
know",[Q33_INSURANCE]="04","Access",[Q33_INSURANCE]="05","Aetna/US
Healthcare" ... "Other")

However, there is a much, much easier approach. Just make a simple
table, if you haven't already got such, with ProviderCode and
ProviderDescription (or whatever field names suit). Then, you jus
add
this table to your query, join to the Q33_Insurance field in you
main
table from the ProviderCode field in the lookup table, and simpl
use
the ProviderDescription directly in your query, forms, reports, etc.

--
Steve Schapel, Microsoft Access MVP


Robert said:
I'm using Microsoft Access 2002 and have a query that
looks at field Q33_INSURANCE and populates the new field
(Health Insurance Provider) based on the data within
Q33_INSURANCE. I knew that there might be a limitation on
the complexity of the expression, but was wondering if
there is another way to go about this. Below if the
expression that I'm using in the query:


Health Insurance Provider: IIf([Q33_INSURANCE]
="01","None - Do not have health insurance",IIf
([Q33_INSURANCE]="02","Do not know",IIf([Q33_INSURANCE]
="04","Access",IIf([Q33_INSURANCE]="05","Aetna/US
Healthcare",IIf([Q33_INSURANCE]="06","Gateway",IIf
([Q33_INSURANCE]="07","Health America/Health
Assurance",IIf([Q33_INSURANCE]="08","Highmark/Blue
Cross/Blue Shield",IIf([Q33_INSURANCE]="09","Keystone",IIf
([Q33_INSURANCE]="10", "Medicare/Medicaid",IIf
([Q33_INSURANCE]="11", "Self",IIf([Q33_INSURANCE]
="12", "UPMC",IIf([Q33_INSURANCE]="13", "VA", IIf
([Q33_INSURANCE]="14", "HMO", IIf([Q33_INSURANCE]
="15", "Commercial", IIf([Q33_INSURANCE]
="16", "Other", "Invalid Health Insurance Provider
Code"))))))))))))


Thanks for any help in advance!
Click to expand...
Click to expand...


-
kbat
 
J

John Spencer (MVP)

Add a NEW Table called tblFormTypes.
fldFormNumber - store the form number 1001 to 1015 (or whatever)
fldFormName - store the name associated with the form number
(total 15 records)

NOW add that table to your queries. And Join the tables on the formTypes field
and the fldFormNumber. Now add the fldFormname to the fields to be displayed.
I am new to forums so I don't know how to put my 2 cents worth in here.
But I have the identical problem to Bob and Steve says to create a
simple table, etc. My problem is that I have a table with about 4000
records, and a field called formTypes which allocate a particular
formtype to each record( 15 different formtypes in all), eg, formtype
1001, 1002, 1003 etc. What I want to do is create a calculated query
that allocates a form name to the formtypes to make the report more
user friendly eg, if formtype - 1001, formname= Form A, if formtype =
1002, formname=Form B, etc. By the time I allocate 15 different
formtypes and formnames, I get "expression too complex". I dont know
how creating a new table and having to do it all again will help. Can
someone please explain it to me in terms that a 2 year old would
understand? I'm really green at this stuff.

Steve said:
*Robert,

There is a limit to the number of nested IIf expressions allowed in
a
query, I think it's 7. I think you could use the Switch function
instead, e.g.
Health Insurance Provider: Switch([Q33_INSURANCE]="01","None - Do
not
have health insurance",[Q33_INSURANCE]="02","Do not
know",[Q33_INSURANCE]="04","Access",[Q33_INSURANCE]="05","Aetna/US
Healthcare" ... "Other")

However, there is a much, much easier approach. Just make a simple
table, if you haven't already got such, with ProviderCode and
ProviderDescription (or whatever field names suit). Then, you just
add
this table to your query, join to the Q33_Insurance field in your
main
table from the ProviderCode field in the lookup table, and simply
use
the ProviderDescription directly in your query, forms, reports, etc.

--
Steve Schapel, Microsoft Access MVP


Robert said:
I'm using Microsoft Access 2002 and have a query that
looks at field Q33_INSURANCE and populates the new field
(Health Insurance Provider) based on the data within
Q33_INSURANCE. I knew that there might be a limitation on
the complexity of the expression, but was wondering if
there is another way to go about this. Below if the
expression that I'm using in the query:


Health Insurance Provider: IIf([Q33_INSURANCE]
="01","None - Do not have health insurance",IIf
([Q33_INSURANCE]="02","Do not know",IIf([Q33_INSURANCE]
="04","Access",IIf([Q33_INSURANCE]="05","Aetna/US
Healthcare",IIf([Q33_INSURANCE]="06","Gateway",IIf
([Q33_INSURANCE]="07","Health America/Health
Assurance",IIf([Q33_INSURANCE]="08","Highmark/Blue
Cross/Blue Shield",IIf([Q33_INSURANCE]="09","Keystone",IIf
([Q33_INSURANCE]="10", "Medicare/Medicaid",IIf
([Q33_INSURANCE]="11", "Self",IIf([Q33_INSURANCE]
="12", "UPMC",IIf([Q33_INSURANCE]="13", "VA", IIf
([Q33_INSURANCE]="14", "HMO", IIf([Q33_INSURANCE]
="15", "Commercial", IIf([Q33_INSURANCE]
="16", "Other", "Invalid Health Insurance Provider
Code"))))))))))))


Thanks for any help in advance! *
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

Andrea Powers Resume 0
expression is too complex to be evaluated 0
DOWNLOAD GibbsCAM 2009,Autocad 2009,WILCOM 2009,CAMWORKS 2009,CATIA2009,Medical software,MASTERCAM X 0

Top