Multivalue criteria search in Query (1 Viewer)

[morpheus2002]

I have a multivalue criteria inside a listbox that I need to use to filter several data out of a table by using a query. The multivalue textfield is the 3rd column of the listbox (eg. John Jonson,Tom Boost,Kim Moore). When I select a row inside the listbox, I want all the adressess of the people that are mentioned in the 3rd column when I click on a button.
How do I make this criteria for a query to get the information I wanted?

 

[RichB]

In your combo box properties you will have a bound column. Change that to the column of the data you want.

 

[morpheus2002]

I already have the 3rd column bound, but the problem is that the value in the third column is sometimes a multivalue. How do I seperate the data of this multivalue field.

When there is only one name in the field, the query will work and gives me the adress I need, but when there are more than one names in the field, the query will give null as a result.

e.g.John Jonson,Tom Boost,Kim Moore --> these names are in one field in the third column, and I want to use them to select their adressess. Is there a way to separate them to put them in a query?

 

[RV]

I already have the 3rd column bound, but the problem is that the value in the third column is sometimes a multivalue

You shouldn't be storing multiple data in one and the same column.
Split your data, multi value is a NO GO in relational databases!
After splitting, use a list box and the multi select option that comes with it.

RV

 

[RichB]

I totally agree with RV. Multiple data in a field defeats the purpose of a relational database. You may need to restructure some things.

 

[morpheus2002]

I totally agree to register data seperately instead of sharing them in one field, but I will explain why there is sometimes more than one value in my field.

I have created a database for Fire Insurance. When the insured didn’t pay his premium, I need to send a letter to the insured, his broker and a copy to all the companies who are coassured in the policy.

The problem is that the companies who are coassured in the policy, were never registered in the database. So I created a multivalue listbox where I need to select manually those companies.
After this multiselection I wrote code in VBA to fill in a table (tblLetters) where I register the letters that need to be send.

Here is the code I used to get the multiselection of the companies in one string to fill in the field in the table

'Declare Variables
Dim varCompanies As Variant
Dim Choice As Variant

varCompanies = ""

For Each Choice In Me.lstCompanies.ItemsSelected
varCompanies = varCompanies & Me.lstCompanies.ItemData(Choice) & ","

Next Choice

varCompanies = Left(varCompanies, Len(varCompanies) - 1)

I now created another listbox with the data from tblLetter. From this listbox I want to create a query to find the related addresses of the companies that were registered in the table.

The reason I work this way is because the letters are made in Ms Word, and I need a visible query for it, am I right?

Or is there a way to get the variable I used for the multiselection to fill in the table to use also as the query criteria?