The expression you entered is too complex

  • Thread starter Thread starter Penny Meholick
  • Start date Start date
P

Penny Meholick

Is there a way to do a lookup for this, rather than the complex expression
I've written? It used to work fine, until I added one more Iif to it.

=IIf([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",IIf([ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",IIf([ClinicCode]="BWC","DuBois Regional Medical Center, PCA Brockway,
1200 Wood St., Brockway, PA 15824",IIf([ClinicCode]="ECR","DRMC
Endocrinology Clinic, 145 Hospital Ave. Suite 101, DuBois, PA
15801.",IIf([ClinicCode]="PNC","DRMC Maternal Fetal Medicine, Medical Arts
Building, Suite 103, DuBois, PA 15801",IIf([ClinicCode]="SBB","DRMC
Orthopaedics, 145 Hospital Ave. Suite 311, DuBois, PA
15801.",IIf([ClinicCode]="LIB","DuBois Regional Medical Center, PCA Liberty,
145 Hospital Ave. Suite 314, DuBois, PA 15801",IIf([ClinicCode]="MJK","DRMC
Neurology,145 Hospital Ave., Suite 311 Medical Arts Bldg., DuBois, PA
15801",IIf([ClinicCode]="SMP","DuBois Regional Medical Center, Surgical
Medical Practice, 629 South Main St., DuBois, PA
15801",IIf([ClinicCode]="PEN","DuBois Regional Medical Center, PCA Penfield,
PO Box 305, Penfield, PA 15849",IIf([ClinicCode]="PTC","DuBois Regional
Medical Center, Penn Township Rural Health Clinic, RR#1, Box 45A, Grampian,
PA 16838",IIf([ClinicCode]="SSP","DuBois Regional Medical Center, Schachter
Surgical Practice, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="RPM","DuBois Regional Medical Center,
Gastroenterology Clinic, 621 South Main Street., DuBois, PA
15801",IIf([ClinicCode]="SMK","DuBois Regional Medical Center, Dr. Kruk's
Office, Medical Arts Bldg. Suite 215, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="CUR","DuBois Regional Medical Center, PCA
Curwensville, 465 State St. Suite A, Curwensville, PA 16833")))))))))))))))
 
Penny,
It may be that you have reached the limit of levels for an IIF statement. If
so you might take a look at the Switch function. It's a little easier to
construct and read. It's syntax is like this:

SWITCH([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",[ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",....)

Another possibility is that it looks like you left out the ELSE part of the
inner most IIF statement.
 
It seems SWITCH will only allow 7 choices.

And I'm at the limit of the IIF statement, also.

Now, I'm at a loss as to how I will handle one more choice here.


Lynn Trapp said:
Penny,
It may be that you have reached the limit of levels for an IIF statement. If
so you might take a look at the Switch function. It's a little easier to
construct and read. It's syntax is like this:

SWITCH([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",[ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",....)

Another possibility is that it looks like you left out the ELSE part of the
inner most IIF statement.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Penny Meholick said:
Is there a way to do a lookup for this, rather than the complex expression
I've written? It used to work fine, until I added one more Iif to it.

=IIf([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",IIf([ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",IIf([ClinicCode]="BWC","DuBois Regional Medical Center, PCA Brockway,
1200 Wood St., Brockway, PA 15824",IIf([ClinicCode]="ECR","DRMC
Endocrinology Clinic, 145 Hospital Ave. Suite 101, DuBois, PA
15801.",IIf([ClinicCode]="PNC","DRMC Maternal Fetal Medicine, Medical Arts
Building, Suite 103, DuBois, PA 15801",IIf([ClinicCode]="SBB","DRMC
Orthopaedics, 145 Hospital Ave. Suite 311, DuBois, PA
15801.",IIf([ClinicCode]="LIB","DuBois Regional Medical Center, PCA Liberty,
145 Hospital Ave. Suite 314, DuBois, PA 15801",IIf([ClinicCode]="MJK","DRMC
Neurology,145 Hospital Ave., Suite 311 Medical Arts Bldg., DuBois, PA
15801",IIf([ClinicCode]="SMP","DuBois Regional Medical Center, Surgical
Medical Practice, 629 South Main St., DuBois, PA
15801",IIf([ClinicCode]="PEN","DuBois Regional Medical Center, PCA Penfield,
PO Box 305, Penfield, PA 15849",IIf([ClinicCode]="PTC","DuBois Regional
Medical Center, Penn Township Rural Health Clinic, RR#1, Box 45A, Grampian,
PA 16838",IIf([ClinicCode]="SSP","DuBois Regional Medical Center, Schachter
Surgical Practice, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="RPM","DuBois Regional Medical Center,
Gastroenterology Clinic, 621 South Main Street., DuBois, PA
15801",IIf([ClinicCode]="SMK","DuBois Regional Medical Center, Dr. Kruk's
Office, Medical Arts Bldg. Suite 215, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="CUR","DuBois Regional Medical Center, PCA
Curwensville, 465 State St. Suite A, Curwensville, PA 16833")))))))))))))))
Click to expand...
Click to expand...
 
In that CASE (pun intended) you will probably need to write a CASE statement
in a function to return the value that you need.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Penny Meholick said:
It seems SWITCH will only allow 7 choices.

And I'm at the limit of the IIF statement, also.

Now, I'm at a loss as to how I will handle one more choice here.


Lynn Trapp said:
Penny,
It may be that you have reached the limit of levels for an IIF
Click to expand...
statement.
If
so you might take a look at the Switch function. It's a little easier to
construct and read. It's syntax is like this:

SWITCH([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",[ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",....)

Another possibility is that it looks like you left out the ELSE part of the
inner most IIF statement.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Penny Meholick said:
Is there a way to do a lookup for this, rather than the complex expression
I've written? It used to work fine, until I added one more Iif to it.

=IIf([ClinicCode]="DUB","DuBois Regional Medical Center, PCA Medical Arts,
PO Box 447, DuBois, PA 15801.",IIf([ClinicCode]="FOR","DuBois Regional
Medical Center, PCA Force, PO Box 143, Force, PA
15841",IIf([ClinicCode]="BWC","DuBois Regional Medical Center, PCA Brockway,
1200 Wood St., Brockway, PA 15824",IIf([ClinicCode]="ECR","DRMC
Endocrinology Clinic, 145 Hospital Ave. Suite 101, DuBois, PA
15801.",IIf([ClinicCode]="PNC","DRMC Maternal Fetal Medicine, Medical Arts
Building, Suite 103, DuBois, PA 15801",IIf([ClinicCode]="SBB","DRMC
Orthopaedics, 145 Hospital Ave. Suite 311, DuBois, PA
15801.",IIf([ClinicCode]="LIB","DuBois Regional Medical Center, PCA Liberty,
145 Hospital Ave. Suite 314, DuBois, PA 15801",IIf([ClinicCode]="MJK","DRMC
Neurology,145 Hospital Ave., Suite 311 Medical Arts Bldg., DuBois, PA
15801",IIf([ClinicCode]="SMP","DuBois Regional Medical Center, Surgical
Medical Practice, 629 South Main St., DuBois, PA
15801",IIf([ClinicCode]="PEN","DuBois Regional Medical Center, PCA Penfield,
PO Box 305, Penfield, PA 15849",IIf([ClinicCode]="PTC","DuBois Regional
Medical Center, Penn Township Rural Health Clinic, RR#1, Box 45A, Grampian,
PA 16838",IIf([ClinicCode]="SSP","DuBois Regional Medical Center, Schachter
Surgical Practice, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="RPM","DuBois Regional Medical Center,
Gastroenterology Clinic, 621 South Main Street., DuBois, PA
15801",IIf([ClinicCode]="SMK","DuBois Regional Medical Center, Dr. Kruk's
Office, Medical Arts Bldg. Suite 215, 145 Hospital Ave., DuBois, PA
15801",IIf([ClinicCode]="CUR","DuBois Regional Medical Center, PCA
Curwensville, 465 State St. Suite A, Curwensville, PA 16833")))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top