using a multi-value Lookup field in a WHERE clause under ADODB

Question

How do you select rows based on a multi-value Lookup field in an Access database?

The below ASP code returns no value when using multi-data column:

<%
Dim Contractors
Dim Contractors_cmd
Dim Contractors_numRows

Set Contractors_cmd = Server.CreateObject ("ADODB.Command")
Contractors_cmd.ActiveConnection = MM_Listings_STRING
Contractors_cmd.CommandText = "SELECT * FROM Contractor WHERE UserStatus = 'Active' AND     AgentID= "& Session("AgentID") 
Contractors_cmd.Prepared = true

Set Contractors = Contractors_cmd.Execute
Contractors_numRows = 0
%>

I must admit it's a while since I used [tag:ms-access] and even longer since I dabbled with it for web development, but I've never heard of a *"multi-data"* column is that the term used in Microsoft Access? Do you mean a *"Lookup Field"*? If so [this](http://stackoverflow.com/a/16379369/692942) might be useful. — user692942, May 26 '14 at 17:03
Yes Lookup Field sorry. The field is already created in the database but cant seem to have it shown. Not to sure if the select from code differs? — user3501463, May 26 '14 at 17:12

score 0 · Accepted Answer · answered May 26 '14 at 18:36

0

If UserStatus is a multi-value Lookup field then the individual value(s) of that field are accessed via UserStatus.Value so you'll need to use

... WHERE UserStatus.Value = 'Active' ...

answered May 26 '14 at 18:36

Gord Thompson

116,920
32
215
418

Thanks!! Can you please assist with the insert function into a multi-value field with two or three values? – user3501463 May 26 '14 at 19:01
@user3501463 That's a different question. Please start a [new question](http://stackoverflow.com/questions/ask) for that. – Gord Thompson May 26 '14 at 19:22

using a multi-value Lookup field in a WHERE clause under ADODB

1 Answers1

Linked