Super complex if statement

W

WatEva

I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And
Mid([Number],2,1)=0,"Pearl",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=1,"White",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=2,"Gold",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=3,"Orange",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=4,"Red",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=5,"Violet",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=6,"Blue",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=7,"Blue-Green",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=8,"Green", IIf(Left([Number],1)=9 And
Mid([Number],2,1)="Y","Gold",IIf(Left([Pigments],10)="MagnaPearl","n/a",IIf(Left([Number],1)="1","Pearl",IIf(Left([Number],1)="2","Gold",IIf(Left([Number],1)="3","Orange",IIf(Left([Number],1)="4","Red",IIf(Left([Number],1)="5","Violet",IIf(Left([Number],1)="6","Blue",IIf(Left([Number],1)="7","Blue-Green",IIf(Left([Number],1)="8","Green",IIf(Left([Number],1)="T","Turquoise",IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
G

Golfinray

Try using Case statements. That is too complex an IIF statement. And once you
get to 2048 characters, IIF will no longer work. That may be your problem.
 
W

WatEva

Can you show me an example in VB code? I only know a little about
programming, haven't done any in vb.

Thanks A bunch!

Golfinray said:
Try using Case statements. That is too complex an IIF statement. And once you
get to 2048 characters, IIF will no longer work. That may be your problem.

WatEva said:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And
Mid([Number],2,1)=0,"Pearl",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=1,"White",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=2,"Gold",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=3,"Orange",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=4,"Red",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=5,"Violet",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=6,"Blue",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=7,"Blue-Green",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=8,"Green", IIf(Left([Number],1)=9 And
Mid([Number],2,1)="Y","Gold",IIf(Left([Pigments],10)="MagnaPearl","n/a",IIf(Left([Number],1)="1","Pearl",IIf(Left([Number],1)="2","Gold",IIf(Left([Number],1)="3","Orange",IIf(Left([Number],1)="4","Red",IIf(Left([Number],1)="5","Violet",IIf(Left([Number],1)="6","Blue",IIf(Left([Number],1)="7","Blue-Green",IIf(Left([Number],1)="8","Green",IIf(Left([Number],1)="T","Turquoise",IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
D

Douglas J. Steele

Can you not just create a table with three columns (Pigment name, pigment
number and colour), and join that table to your existing table?
 
W

WatEva

You mean write the color myself instead of letting the querry figure it out?

This is the example of the Pigment field and the Number field:
Red 639M

Douglas J. Steele said:
Can you not just create a table with three columns (Pigment name, pigment
number and colour), and join that table to your existing table?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


WatEva said:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it
is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And
Mid([Number],2,1)=0,"Pearl",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=1,"White",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=2,"Gold",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=3,"Orange",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=4,"Red",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=5,"Violet",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=6,"Blue",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=7,"Blue-Green",IIf(Left([Number],1)=9 And
Mid([Number],2,1)=8,"Green", IIf(Left([Number],1)=9 And
Mid([Number],2,1)="Y","Gold",IIf(Left([Pigments],10)="MagnaPearl","n/a",IIf(Left([Number],1)="1","Pearl",IIf(Left([Number],1)="2","Gold",IIf(Left([Number],1)="3","Orange",IIf(Left([Number],1)="4","Red",IIf(Left([Number],1)="5","Violet",IIf(Left([Number],1)="6","Blue",IIf(Left([Number],1)="7","Blue-Green",IIf(Left([Number],1)="8","Green",IIf(Left([Number],1)="T","Turquoise",IIf(Left([Number],1)="0","Blue
Pearl")))))))))))))))))))))
 
J

John Spencer

This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
W

WatEva

I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

John Spencer said:
This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
J

John Spencer

You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

John Spencer said:
This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
W

WatEva

I used the iif statement inside a query.

I dont think I can use the switch statement in a query.

John Spencer said:
You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

John Spencer said:
This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
J

John Spencer

Did you try? If so, did you get an error message?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I used the iif statement inside a query.

I dont think I can use the switch statement in a query.

John Spencer said:
You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

:

This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
W

WatEva

Yes I tried and I got the error message :(
I need to finish this by tuesday :(
I am gonna try doing the vb thing but I have no idea where to put the
statement and how to pass it and how to declare variables and what variables
to declare.

John Spencer said:
Did you try? If so, did you get an error message?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I used the iif statement inside a query.

I dont think I can use the switch statement in a query.

John Spencer said:
You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

:

This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
J

John Spencer

Public Function fGetTheColor(TheNumber, ThePigment)

IF Left(TheNumber,2) = "90" Then
fGetTheColor = "Pearl"
ELSEIF Left(TheNumber,2) = "91" Then
fGetTheColor="White"
ELSEIF Left(TheNumber,2) ="92" Then
fGetTheColor="Gold"
ELSEIF ...
...
ELSEIF Left(ThePigment,10) = "MagnaPearl" Then
fGetTheColor = "N/A"
ELSEIF ...
...
ElseIf Left(TheNumber,1) = "T" Then
fGetTheColor = "Turquoise"
ElseIf Left(TheNumber,1) = "0" Then
fgetTheColor="Blue Pearl"
End IF

End Function

In the query you would use

Field: Color: fGetTheColor({Number],{Pigments])

Good luck.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Yes I tried and I got the error message :(
I need to finish this by tuesday :(
I am gonna try doing the vb thing but I have no idea where to put the
statement and how to pass it and how to declare variables and what variables
to declare.

John Spencer said:
Did you try? If so, did you get an error message?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I used the iif statement inside a query.

I dont think I can use the switch statement in a query.

:

You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

:

This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 
W

WatEva

It works.

Thanks so much. You Rock!!

John Spencer said:
Public Function fGetTheColor(TheNumber, ThePigment)

IF Left(TheNumber,2) = "90" Then
fGetTheColor = "Pearl"
ELSEIF Left(TheNumber,2) = "91" Then
fGetTheColor="White"
ELSEIF Left(TheNumber,2) ="92" Then
fGetTheColor="Gold"
ELSEIF ...
...
ELSEIF Left(ThePigment,10) = "MagnaPearl" Then
fGetTheColor = "N/A"
ELSEIF ...
...
ElseIf Left(TheNumber,1) = "T" Then
fGetTheColor = "Turquoise"
ElseIf Left(TheNumber,1) = "0" Then
fgetTheColor="Blue Pearl"
End IF

End Function

In the query you would use

Field: Color: fGetTheColor({Number],{Pigments])

Good luck.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Yes I tried and I got the error message :(
I need to finish this by tuesday :(
I am gonna try doing the vb thing but I have no idea where to put the
statement and how to pass it and how to declare variables and what variables
to declare.

John Spencer said:
Did you try? If so, did you get an error message?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I used the iif statement inside a query.

I dont think I can use the switch statement in a query.

:

You would use it like you use the IIF statement.

BUT once you reach the limit on the number of characters it is going to
fail just like the IIF statement.

Color: SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ..., [Pigments] Like "MagnaPearl*","n/a", ...)

Check the VBA help for the Switch function. It basically is a series of
matched pairs - the first element of a pair is a test and the second
element is what to return if the pair is true. The first time a true is
returned that corresponding value from the second half of the pair is
returned.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I probably sound like an idiot but do I just copy and past the Switch
statement? Where in the module should I put it? In the form where i am using
it?

:

This look like you need a table to translate the Pigment Name and number
to a color name. Perhaps a table like the following.

TblColors
NumberPart
Pigment
ColorName

90*;*;Pearl
91*;*;White
92*;*;Gold
....
98*;*;Green
9Y*;*;Gold
10*;*;MagnaPearl
*;MagnaPearl;n/a
1*;*;Pearl
2*;*;Gold
....
T*;*;Turquoise
0*;*;Blue Pearl

Then to get the color you could use the DLookup function or you could
include tblColors in the query with a non-equi join using the like operator.

DLookup("ColorName","tblColors", "'" & Left(Number,2) & "' LIKE
Numberpart AND '" & Pigments & "' LIKE Pigment")

Your other choice would be to build a VBA function and pass the number
and pigment into the function and then use it to select the color and
return it.

You could also try using the switch function instead of the the nested
IIF statements.

SWITCH(Left(Number,2)=90,"Pearl", Left(Number,2)="91",White,
Left(Number,2)="92","Gold", ...

Eventually you will run into a limit on the length of the text you can
put into a calculated field.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


WatEva wrote:
I am a newbie so go easy....
I have a really complex if statment that I am using in a query. Basically
its looking at the Pigment name and Number and figuring out what color it is.
For some pigments, the first digit and for some its the second digit that
define the color. Here is my if statement that brings the error message
"Expression is too complex".

And this isn't all, theres more i need to add :-/

Color: IIf(Left([Number],1)=9 And Mid([Number],2,1)=0,"Pearl"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=1,"White"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=2,"Gold"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=3,"Orange"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=4,"Red"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=5,"Violet"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=6,"Blue"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=7,"Blue-Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)=8,"Green"
,IIf(Left([Number],1)=9 And Mid([Number],2,1)="Y","Gold",
IIf(Left([Pigments],10)="MagnaPearl","n/a",
IIf(Left([Number],1)="1","Pearl",
IIf(Left([Number],1)="2","Gold"
,IIf(Left([Number],1)="3","Orange",
IIf(Left([Number],1)="4","Red"
,IIf(Left([Number],1)="5","Violet"
,IIf(Left([Number],1)="6","Blue"
,IIf(Left([Number],1)="7","Blue-Green"
,IIf(Left([Number],1)="8","Green"
,IIf(Left([Number],1)="T","Turquoise"
,IIf(Left([Number],1)="0","Blue Pearl")))))))))))))))))))))
 

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

Top