VBA equivalent to Access query criteria "in"

  • Thread starter Thread starter Amelia
  • Start date Start date
A

Amelia

I have a reference table with various criteria that I'd
like to keep short.

The idea is to read each record of a data table, and check
to see if it meets certain qualifications of the reference
table, and retrieve the MaxAmt value for the highest level
that it qualifies for.

I'd like the reference table to include criteria that
would be read as a list, e.g.,
[Field1] in (1,2,3)

I'd also like to another field with a list, e.g.,
cat,dog,bird
and an accompanying field with "in" or "not in"
so that these could be concatenated together to compose a
criteria string
[Field2] in (cat,dog,bird]

Here is an example of the criteria table values:
CritCat1 Crit_Cat2 CritType_Cat3 Crit_Cat3
A1 2 In cat,dog
A1 1,2 not in cat
A2 2,3 In cat,dog,bird

Here is an example of the fields in the data table:
Cat1 Cat2 Cat3
A1 1 cat
A2 2 bird
A1 1 dog

Any suggestions?
 
Although you cannot use "field in (values)" immediately in VBA, you can
have Eval() process it. See Help on Eval for ideas.
I'd like the reference table to include criteria that
would be read as a list, e.g.,
[Field1] in (1,2,3)
Click to expand...
 
Back
Top