Multivalue field in subform as criteria in query

[BrianWen]

Hi all!

First of all, I know of the implications of using multivalue fields. The data I store in the field is on the fly data (what the user has selected for filtering).

So, let me first explain the setup:

1. Table (filter) that contains a few fields that for each user contains what the user has selected to filter in a form (query). One of those fields is a multivalue field, so that they can select 5,7 and 8 for instance.

2. Table with main data to be displayed via a form.

3. The form that displays the main data. The form's data source is a query, that has criteria, so that what the user has in the filter query is what is displayed from the main data table.

4. Filter form bound to the filter table. This a put on the main data form as a subform in the form header.

So, the subform with the bound multivalue field is displayed through the main data form. I change select some values in the multi value field and some code requeries the main data, which is based on a query, that looks in the subform control. I don't get the popup asking a value but I get no records, even though there are of course matching records.

My query has this criteria on the appropriate field: Forms![Dataform]![Filter_subform].Form![multivaluefield]

Got any good ideas on what could be wrong?

Please ask if I missed some important info.

 

[MajP]

I have never tried this so I am not certain. But I think you may have to use the value property of the multi valued field in your criteria. See link

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx

 

[BrianWen]

Problem is I'm not using the multivalue field as criteria in the query, but instead a control bound to the field.

It doesn't complain of this as criteria either, but it doesn't return any records:

Forms![Dataform]![Filter_subform].Form![multivaluefield].Value

 

[MajP]

I have no idea what the value of a control bound to a multivalued field. It is a non null variant, but that is about it as far as I can tell. You can test this in code by trying to return the value of a control bound to a multivalued field. I could not find any info on this. Maybe someone else knows.

So I doubt that the value of the control can be used as a criteria in another query.

The value of the multi valued field is a recordset. But as far as I can tell the value of the control is not a recordset. If this was me, and I had to do this I would build the sql string in code instead of trying to use the control as a criteria. You can return the recordset of the field, loop the records, and build the criteria.

 

[MajP]

I cannot figure out how to use the value of an MV control as a criteria for a query. But I can build a function that turns the value of the MV field into a criteria to use in a query.

Public Function getFilterString(mvField As Variant, Optional andOr As String = "AND")
  Dim aStr() As String
  Dim str As String
  Dim i As Integer
  aStr = Split(mvField, ",")
  andOr = " " & andOr & " "
  For i = 0 To UBound(aStr)
    aStr(i) = "'" & Trim(aStr(i)) & "'"
    If str = "" Then
      str = aStr(i)
    Else
      str = str & andOr & aStr(i)
    End If
  Next i
  getFilterString = str
End Function

I am using the products table from Northwind. So if I can select products I can build a string like

'Aniseed Syrup' AND 'Boston Crab Meat' AND 'Camembert Pierrot' AND 'Carnarvon Tigers' AND 'Chai'

That can be used to query the multivalue field or any field.

Note: Need to add error checking for passing a Null value of the muli value field.

 

[BrianWen]

As far as I know it's not neccesary to construct a long SQL manually by looping over the multivalue recordset. I believe can be 'In(controlname)'.

 

[MajP]

I doubt it. As I have proven the control value is not a string. You might be able to do it the field value which is a comma seperated string. However you cannot not use a field value in a query without a custom function to return the field value.