Query too complex

  • Thread starter Thread starter JGS
  • Start date Start date
J

JGS

I am trying to run:
mod1: IIf([procmod1] Not In ("00","26","62","80",
"8T","AA","AD","GY","ST","TC","QK","QS","QX", "QY","QZ"),"0
0",
IIf(([procmod1]="00"),"00",
IIf(([procmod1]="26"),"26",
IIf(([procmod1]="62"),"62",
IIf(([procmod1]="80"),"80",
IIf(([procmod1]="8T"),"8T",
IIf(([procmod1]="AA"),"AA",
IIf(([procmod1]="AD"),"AD",
IIf(([procmod1]="GY"),"GY",
IIf(([procmod1]="ST"),"ST",
IIf(([procmod1]="TC"),"TC",
IIf(([procmod1]="QK"),"QK",
IIf(([procmod1]="QS"),"QS",
IIf(([procmod1]="QX"),"QX",
IIf(([procmod1]="QY"),"QY",
IIf(([procmod1]="QZ"),"QZ",
IIf(([procmod1] Is Null),"00"))))))))))))))))

I can run smaller versions but when I add all the
arguments I need it is too complex. I know there is a way
to do this with visual basic and I am just now teaching
myself visual basic. Can anyone supply me with a walk
though to help. Data base = data1, file = file1
I am using data from a column named procmod1 and
displaying the results in mod1.

Help?
 
You are creating a maintenance nightmare with huge expressions like this.
The best solution would use a lookup table of all possible values of
procmod1 and a corresponding mod1 value. This would make the solution all
data driven.

The next best alternative is to create a function:
Public Function GetMod1( pvarProcMod1 as Variant) as String
pvarProcMod1 = pvarProcMod1 & ""
Select Case pstrProcMod1
Case "26","62","80", "8T","AA","AD","GY","ST","TC","QK","QS","QX",
"QY","QZ"
GetMod1 = pvarProcMod1
Case Else
GetMod1 = "00"
End Select
End Function

Save the above code in a new, blank module and save the module as
"modStringStuff".
You can then use this function in your query like
Mod1:GetMod1([procmod1])
 
I am trying to run:
mod1: IIf([procmod1] Not In ("00","26","62","80",
"8T","AA","AD","GY","ST","TC","QK","QS","QX", "QY","QZ"),"0
0",
IIf(([procmod1]="00"),"00",
IIf(([procmod1]="26"),"26",
IIf(([procmod1]="62"),"62",
IIf(([procmod1]="80"),"80",
IIf(([procmod1]="8T"),"8T",
IIf(([procmod1]="AA"),"AA",
IIf(([procmod1]="AD"),"AD",
IIf(([procmod1]="GY"),"GY",
IIf(([procmod1]="ST"),"ST",
IIf(([procmod1]="TC"),"TC",
IIf(([procmod1]="QK"),"QK",
IIf(([procmod1]="QS"),"QS",
IIf(([procmod1]="QX"),"QX",
IIf(([procmod1]="QY"),"QY",
IIf(([procmod1]="QZ"),"QZ",
IIf(([procmod1] Is Null),"00"))))))))))))))))

I can run smaller versions but when I add all the
arguments I need it is too complex. I know there is a way
to do this with visual basic and I am just now teaching
myself visual basic. Can anyone supply me with a walk
though to help. Data base = data1, file = file1
I am using data from a column named procmod1 and
displaying the results in mod1.
Click to expand...

The problem is that you're nesting too deeply, and going all around
the barn to set the value to Procmod1 most of the time. You don't need
to set it to "26" if it's already "26"!

Try

IIf([procmod1] In ("00","26","62","80","8T","AA","AD","GY",
"ST","TC","QK","QS","QX", "QY","QZ"), [procmod1], "00")
 
I definitely agree with Duane and obviously his function solution should work
and would be easy to modify if you needed it.

That said, have you considered rewriting the IIF statement?

mod1: IIf(([procmod1] & "") In
("00","26","62","80","8T","AA","AD","GY","ST","TC","QK","QS","QX", "QY","QZ"),
ProcMod1,"00")

Duane said:
You are creating a maintenance nightmare with huge expressions like this.
The best solution would use a lookup table of all possible values of
procmod1 and a corresponding mod1 value. This would make the solution all
data driven.

The next best alternative is to create a function:
Public Function GetMod1( pvarProcMod1 as Variant) as String
pvarProcMod1 = pvarProcMod1 & ""
Select Case pstrProcMod1
Case "26","62","80", "8T","AA","AD","GY","ST","TC","QK","QS","QX",
"QY","QZ"
GetMod1 = pvarProcMod1
Case Else
GetMod1 = "00"
End Select
End Function

Save the above code in a new, blank module and save the module as
"modStringStuff".
You can then use this function in your query like
Mod1:GetMod1([procmod1])

--
Duane Hookom
MS Access MVP

JGS said:
I am trying to run:
mod1: IIf([procmod1] Not In ("00","26","62","80",
"8T","AA","AD","GY","ST","TC","QK","QS","QX", "QY","QZ"),"0
0",
IIf(([procmod1]="00"),"00",
IIf(([procmod1]="26"),"26",
IIf(([procmod1]="62"),"62",
IIf(([procmod1]="80"),"80",
IIf(([procmod1]="8T"),"8T",
IIf(([procmod1]="AA"),"AA",
IIf(([procmod1]="AD"),"AD",
IIf(([procmod1]="GY"),"GY",
IIf(([procmod1]="ST"),"ST",
IIf(([procmod1]="TC"),"TC",
IIf(([procmod1]="QK"),"QK",
IIf(([procmod1]="QS"),"QS",
IIf(([procmod1]="QX"),"QX",
IIf(([procmod1]="QY"),"QY",
IIf(([procmod1]="QZ"),"QZ",
IIf(([procmod1] Is Null),"00"))))))))))))))))

I can run smaller versions but when I add all the
arguments I need it is too complex. I know there is a way
to do this with visual basic and I am just now teaching
myself visual basic. Can anyone supply me with a walk
though to help. Data base = data1, file = file1
I am using data from a column named procmod1 and
displaying the results in mod1.

Help?
Click to expand...
Click to expand...
 
Back
Top