query a multivalued lookup field

[icccapital]

I have setup a multivalued lookup field in access 2007. I am not sure how or
even if it is possible to query this field.

if I query SELECT clients FROM client; from query design and look at in a
datasheet view, I will get a drop down field which contains everyone in the
list.

More importantly I would like to do it in VBA so i was trying to
openrecordset with this query and nothing was returned to the recordset.

Does anyone have any thoughts? Am I going to have to replace the multivalue
lookup to something else?

 

[Allen Browne]

There are a couple of ways to query an MVF:

a) Add .value after the field name, and then in the Criteria row enter the
value you want to find. For example, if the Field is named City, you'll
have:
Field row: City.Value
Criteria row: "New York"

b) In the criteria row, use IN with a list of values, e.g.:
IN (4,5,6)
or for a text field:
IN ("New York", "Springfield")

If you OpenRecordset, the MVF will itself be a recordset. Assign another
Recordset object to the field.

 

[icccapital]

I understand the querying where I would look for a record where a value
matches one in my MV field, but what I am looking to do is retrieve the
multivalues selected for a particular record.

So maybe your second point, copied below, is what I need. Can you explain
further how you would do that? if I set rst = openrecordset("SELECT city
FROM cities WHERE Person = "jeff"), the recordset opened has nothing in the
fields. thanks

 

[Allen Browne]

In query design view, you can drag either the field name or the field
name.Value into the grid. Experiment with both ways.

If the person really is storing a text value such as "Jeff", that will work
in the .Value criteria.

If you don't use the field.Value approach, you will need the IN operator,
and supply the actualy value (probably a number.)

 

[icccapital]

Sorry, I am not making my point. Let us say that I have a person jeff and
his record contains a multivalue field cities in which he has St. Louis and
Toront o in them. In VBA I need to open a recordset that will return St.
Louis and Toronto from my query where I only know person = "jeff".

Can I do that?

 

[Allen Browne]

Not sure I understand what's the limitation here.

Do you want ONE record for Jeff with both cities?
SELECT Table1.City
FROM Table1
WHERE Table1.Person = "jeff";

Or do you want 2 records if Jeff is associated with 2 cities?
SELECT Table1.City.Value
FROM Table1
WHERE Table1.Person = "jeff";

 

[icccapital]

So in VBA if I open a recordset, I would expect that two records would be
returned, 1 for each city so that rst.fields(0) = city1 and rst.fields(1) =
city2. unless there is another way to get these two cities out of a query in
vba. I need to have it in code. Thanks

 

[Allen Browne]

You will get 2 records if you include the .Value bit.

If you don't you will get a single record, where the City field is itself a
recordset. I take it you have not yet looked at the ELookup() example of how
to handle that.

 

[icccapital]

Thanks Allen that was useful for the query in design view.

For those wanting more info: After some more searching I found "access 2007
vba programmers reference", which had a section on opening the recordset then
setting the field of the multivalue field and then setting the recordset of
that field by using field.value. You can then loop through all the records
of that multivalue field