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")))))))))))))))))))))
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")))))))))))))))))))))