Solved How to use multi-value in query search?

[aref]

Hello
I used the multivalue property to search in the query through the form, but it doesn't work
please help
Thanks

 

[aref]

Attachment:

 

[MajP]

If you decide to do this properly you can achieve that effect using my Multi Select ListBox Filter

Solved - Unbound Combo box need multiple value

Hi, I have a searcher form. It is worked. But now like to put multiple value to search. CODE : Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.fltCompanyName) Then strWhere = strWhere & "([CompanyName] = """ &...

www.access-programmers.co.uk

 

[CJ_London]

it won't work because what you are seeing is formatted as a comma separated string - but without the quote marks required to identify it as a string, What you actually have is a list of colors, one colour per row. You cannot use them as a filter or criteria. As a test, just include [Forms]![Form1]![Color] as a column in your query - it returns nothing

Agree with the other comments, multivalue fields look useful, but you quickly find they have severe limitations. Aside from the fact you can't do what you want to do here, they can be a drag on performance once you have a few thousand records, they can't be indexed and they can't be sorted.

 

[aref]

Thank you for your answers.
What should I do if I want to run an unbound query through the form and different colors inserted in the unbound text box are displayed. The mentioned cases and examples are bounded.

How to use the IN operator in the query for the above search.

 

[CJ_London]

I think what you are talking about requires some code, typically called 'concatrelated' see this link http://allenbrowne.com/func-concat.html

 

[MajP]

Thank you for your answers.
What should I do if I want to run an unbound query through the form and different colors inserted in the unbound text box are displayed. The mentioned cases and examples are bounded.

How to use the IN operator in the query for the above search.

Did you not look at my example, which is super robust and easy to use? I doubt you find a better solution.

 

[aref]

Hello, thank you all for following up
The mentioned examples are great, but they do not solve my problem.
The issue is that:
I have a text box in the form that is for searching.
In this text box, search items are separated with semicolons, such as:
Blue; pink; Purple
When the run query button is clicked and the query is executed, all the records that include:
Blue; pink; Purple
are displayed.
But I don't know where the problem is.
................................................
I can do this manually in the query and it works
In ("Blue"; "pink"; "Purple")
But not through the form.

 

[aref]

Attachment:

 

[arnelgp]

here try you database.

 

[ebs17]

But I don't know where the problem is.

The text box has one content, and that is a string, solely due to the characters used.

strContent = "Blue; pink; Purple"

This is something different than what the query requires. You can do that:

strContent = Replace(strContent, "; ", Chr(34) & ", " & Chr(34))
strContent = "(" & Chr(34) & strContent & Chr(34) & ")"
Debug.Print strContent

For the IN clause, however, you cannot pass the listing in parentheses as a simple parameter. Here you would have to dynamically assemble the SQL statement using VBA.

A slightly different variant is a bit simpler here, but does not offer index use (sometimes you have a lot of data and still want performance). The achieved filter would look like this:

WHERE Instr(1, ";Blue;pink;Purple;", ";" & FieldColor & ";") > 0

' suitable for parameter transfer
WHERE Instr(1, parListing, ";" & FieldColor & ";") > 0

Content preparation:

strContent = ";" & Replace(strContent, " ", "") & ";"
Debug.Print strContent

 

[June7]

Why use GROUP BY in RowSource query when there are no aggregate functions?

 

[MajP]

The whole idea of filtering a multi select field with an unbound multi select field is STUPID!!!
But here you go.