Solved How to use multi-value in query search? (1 Viewer)

[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:

 

[Pat Hartman]

The short answer is don't use Multivalue fields. Do it the right way using a separate table that YOU control.

The long answer is You need to be able to overcome all the obstacles caused by using this abomination data type, PLUS you need to learn a new variation of SQL in order to use these fields in any query. You also need to be aware that you can NEVER upgrade a table with any of the abomination data types to SQL Server. So, if that is something you might ever have to do. Get rid of them NOW. Do not even attempt to figure out how to work with them.

Using Lookup and multivalued fields in queries - Microsoft Support

Learn how to work with multivalue fields in Access queries.

support.microsoft.com

 

[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.

 

[Pat Hartman]

I like @MajP 's solution but here is a simple one that might be easier to implement using an unbound multi-select listbox to build an In() clause.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

 

[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?

 

[Pat Hartman]

Of course, the real solution is to normalize the database rather than trying to find a method that allows you to get around the mistakes in your database design.

@ebs17 explained your problem and gave you a solution. Just keep in mind that the selection will be OR, not AND.

Where Somefield In("Blue"; "pink"; "Purple")
translates to:
Where somefield = "Blue" OR somefield = "pink" OR somefield = "Purple"

 

[MajP]

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