Filtering a Multi-Valued field

[ted.martin]

I have a table field (Group) that is linked to a Multi Select List Box.

I want to open a form, filtered to just ONE of the Groups stored in that Group field.

I have a simple pop up form that allows selection of the desired filter from a combo box of individual Group names and then would like the following code to run when the form opens.


Me.Filter = "[Group] = Like ""*"" & '" & Group.Text & "' & ""*""" ' List Box version

However, I keep getting an error message saying that the multi-valued field 'Group' cannot be used in a Where or Having clause.

In summary, I want to store multiple groups in the main table and then be able to select only one of the Groups from a combo and get the form to open based on the single Group selected from that combo.

Hope this is clear and that someone can guide me through. Thanks.

 

[boblarson]

Using multi-valued fields can be a pain in the butt. It uses a hidden table which you would need to access in order to get the values. Personally, I would not use a multi-valued field but instead use what is generally recommended - a junction table which holds the multiple values and then it is much easier to get the data back out.

You might check out this link (can't tell if it is still live or not as it seems to be blocked by my workplace) which had Access MVP Albert D. Kallal explaining the use of the MVF's.

 

[ted.martin]

Using multi-valued fields can be a pain in the butt. It uses a hidden table which you would need to access in order to get the values. Personally, I would not use a multi-valued field but instead use what is generally recommended - a junction table which holds the multiple values and then it is much easier to get the data back out.

You might check out this link (can't tell if it is still live or not as it seems to be blocked by my workplace) which had Access MVP Albert D. Kallal explaining the use of the MVF's.

Thanks Bob - pain is what I thought. What did cross my mind is that I can DLOOKUP the values in the Multi-Value field e.g. GroupA, GroupB, GroupC and read this into a string variable.

Then do an INSTR on that and if a (LIKE) match for (say) GroupB, then flag the record and filter on this flag. Does this make sense?

For example

Dim myFilter, myStr As String

myStr = "Wigan, Preston, Leeds"


myFilter = "Preston"


If InStr(1, myStr, myFilter, vbTextCompare) = 0 Then
MsgBox "Not Found"
Else
MsgBox InStr(1, myStr, myFilter, vbTextCompare)
End If

 

[ted.martin]

Found a much better solution here that works a treat:

The key reference was in the section "How to employ a multivalued field in a query"


http://office.microsoft.com/en-gb/access-help/guide-to-multivalued-fields-HA001233722.aspx

... and here is my code after creating an additional Flag field in the main table:

Private Sub Group_DblClick(Cancel As Integer)

If Len(Group.Text & "") = 0 Then
DoCmd.Close acForm, "FP-Group"
Exit Sub
End If

Call SetFlag(Group.Text)

strFlag = "Filter"
strFilter = "[Flag] = Yes" 'need to use Form Filter due to Record Source argument



DoCmd.Close acForm, "FP-Group"
DoCmd.OpenForm "F-Contacts", , , , , , "Q-dbContacts"

End Sub

Private Sub SetFlag(myGroup As String)

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE dbContacts SET dbContacts.Flag = No" ' Firstly Reset
DoCmd.SetWarnings True

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Q-Group MultiList", dbOpenDynaset)

With rs
.MoveFirst

Do Until .EOF

If !myGroup = myGroup Then
.Edit
!Flag = -1 ' Yes
.Update
End If

.MoveNext

Loop

End With

Set db = Nothing
Set rs = Nothing

End Sub

 

[vbaInet]

I remember doing this for a poster on here but can't remember where the example is. It was a search form using multivalued fields. You might be able to do:

Me.Filter = "[Group].Value = '" & Group.Text & "'"

Or

Me.Filter = "[Group].Value In ('" & Group.Text & "')"

 

[ted.martin]

I remember doing this for a poster on here but can't remember where the example is. It was a search form using multivalued fields. You might be able to do:

Me.Filter = "[Group].Value = '" & Group.Text & "'"

Or

Me.Filter = "[Group].Value In ('" & Group.Text & "')"

OK - will try and get back to you. A simpler solution; if it works.

 

[vbaInet]

By the way, should it be Group.Text or just Group as a field?

 

[ted.martin]

Not sure but within the query referred to in Microsoft's script it is Group.Value.

Will try both ways.

 

[vbaInet]

I meant the right hand side of the equator. I was following the syntax of your example in your first post:

Me.Filter = "[Group] = Like ""*"" & '" & Group.Text & "' & ""*"""

Maybe that was a typo. In that case ignore the .Text part in my suggestion.

 

[ted.martin]

Thanks vbaInet; finally got round to testing your proposed solution and

strFilter = "[Group].Value In ('" & Group.Text & "')"

works prefectly and avoids the need for the Call routine and all that Flag stuff. Well done and much appreciated.


So 'world of access deveopers' we can indeed filter from a multi-field list box by using one line of code. Thats impressive - so well done.


ps - I use Group.Text as the filter criteria is selected from a double-click of a combo box which is the raw list of all the List Box options.

 

[vbaInet]

Ah good stuff! Glad to know that worked for you Ted.

 

[mdgibbs88]

I know this is an older thread, but it has been quite helpful in helping me build my search screen (which builds a filter string). By request of the user, I am using a multiselect combo box for a data collection. I know this is not the best way to go about it, but I am sort of stuck.


On my search form that I have used on a bunch of different databases with success, I have tried to search the multivalued field with a little success. My results always give me duplicate results (1 each for each multivalued field value). I tried the suggestion above for Value In and .Text and I get an error on the .Text portion. (Compile Error: Method or Data Member not found)


Any suggestions (other than another way to handle the multivalue field please) would be greatly appreciated! Here is the portion of my code that builds this:


If Not IsNull(Me.txtApps) Then


strFilterApps = "([EpicApprovedApplications_MS_TEMP].Value In('" & EpicApprovedApplications_MS_TEMP & "') Like ""*" & Me.txtApps & "*"") AND "
MsgBox strFilterApps
strWhere = strWhere & strFilterApps

End If

 

[isladogs]

I know you don't want to hear this but you will be fighting all the way if you persist with MVFs.
See my answer to this parallel post: https://www.access-programmers.co.uk/forums/showpost.php?p=1608240&postcount=5
Also please read the link: http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763