Conditional error

  • Thread starter Thread starter Bill B
  • Start date Start date
B

Bill B

Hi All

I have a report that is throwing an error on a text field field. below is
the condition. I'm thinking the 'In' condition is causing the problem and I
can't seem to find anything with google regarding the use of 'In' in a VBA
IIF statement.

=IIf([test_typ]="TENS" And [shape]="RECT" And [thick]<=0.16 And [testmeth]
In ("48","41","42","43","44") And Not ([prod_fac] In ("LAB","2XX","3XX"))
And Not ([sps] In
("1156","1189","1203","1209","1227","1235","1165","2507","2541","2546","2547")),
"ROBOTIC",IIf([Shape]="JIS","JIS",IIf([Shape]="API","API","")))

Any suggestions??

Bill
 
Bill,

IN is one of a small number of SQL operators not supported by the VBA
expression service (the others being LIKE and BETWEEN). This doesn't mean
they cannot be used in VBA code at all; for instance they can be used as the
criterion of the DLookup function, where in essence an underlying SQL query
is being called. But not in this context.

An alternative is to change the value list to a single string, delimiting
the individual values with a high ASCII character as a token, then use the
Instr function, reversing the order of the operation e.g. in place of:

Not ([prod_fac] In ("LAB","2XX","3XX"))

use:

Instr("~LAB~2XX~3XX~", "~" & prod_fac & "~") = 0

or in place of:

[testmeth] In ("48","41","42","43","44")

use:

Instr("~48~41~42~43~44~", "~" & testmeth & "~") > 0

As a string comparison operation is being used here, if the values are
numeric, then you'd have to format the value in the field to match the values
in the list of course, e.g.

Instr("~01234~12345~23456~", Format(MyNumber,"~00000~")) > 0

However, I would really regard the above as being of academic interest only
as, rather than resorting to such a convoluted expression as the control's
ControlSource property, I'd be more inclined to write a little VBA function
and call that. In the function you can use Select Case statements to test
values passed into the function as arguments against value lists, on the
basis of which the return value of the function would be determined.

Ken Sheridan
Stafford, England
 
Back
Top