Convert a filter expression to VBA

[sougata666]

I need to convert the following filter expression produced by access in a query to VBA

(Lookup_EquipmentID.EquipmentList="A" AND Lookup_EquipmentID.EquipmentList="B")

 

[pbaldy]

Curious how a field could equal both "A" and "B", but typically in VBA you'd need single quotes:

Variable = "(Lookup_EquipmentID.EquipmentList='A' AND Lookup_EquipmentID.EquipmentList='B')"

 

[sougata666]

Apologies. Here's the complete thing.

I have a query involving multiple tables and sub-queries. Only the equipmentID field is multivalued and using a lookup from another table (containing equipmentlist). It is then indexed along with the customerID.

This query is working fine in query mode. The expression I posted is the access generated filter for displaying a "customer" who can use both A and B (both are in the multivalued field). I copied from the form filter property. Actually, access had put an "OR" between A and B but I changed it to "AND".

Now, the problem comes when I am using it as a subform in a form which lets the user select the filter options using dropboxes and checkboxes (A and B are checkboxes").

The options are converted to a string joined by AND and this is where I am facing an issue.

 

[sougata666]

Junction table is not being used as I want one customerId to come up in case he satisfies both A and B. I don't want two separate entries.

 

[sougata666]

My issue is similar to
http://http://www.access-programmers.co.uk/forums/showthread.php?t=257871
Except that I need the VBA code.

 

[pbaldy]

Rather than delete your thread, how about posting your solution so it could help somebody else later? I haven't used (and likely won't) multivalue fields, so I'm not sure of the answer.