multi select list box and query

[tizwaz]

I have a multi selection list box and I want to run a query based on the selections in this.

I have put the following as my criteria but it returns nothing.

[forms]![frmselectperson]![lstperson].[itemsSelected]

If I revert the list box to a single selection one and change itemsSelected to value it runs ok. am I doing something wrong?

 

[tizwaz]

Ok - I've read a few more posts on here and realise I can't do this via the query directly. I'm now trying to code a hidden text box on the form which has the list box in it. My problem now is writing the SQL. Unfortunately my list box is a list of full names created by combining the fields firstname and surname. Also it only returns distinct records so that the same name doesn't appear several times in the list. How can I create my SQL when fullname isn't a field in the underlying table? I need to return all records for the names selected in the listbox and open a form based on the query.

 

[FancyPrairie]

Try this faq181-5497. It might be a little overkill for what you are attempting. But you can use it in future applications. Simply copy the code from the FAQ and paste it into a new module. The routine (BuildWhere) will return the where clause for you based on the item(s) selected in the multi-select list box.

 

[tizwaz]

Hi there

Thanks a lot for your help but I'm afraid I'm still struggling with this. I've pasted the code into a module and changed the tag on my list box control to read TblIssues.FirstName,string;TblIssues.Surname,string

However it is giving this result when I call the function

(TblIssues.FirstName In ('Adderley Ian', 'Ahmed A', 'Aina Bukky'))

Those are the 3 names I have clicked in the list box but they are the results of column 1 which is the full name (ie calculated field combination of columns 2 and 3 which are the FirstName and Surname) As full name isn't a field in my table how can I get this to work

 

[FancyPrairie]

What does your query look like? You might try something like this:

SELECT FullName
FROM (Select [LastName] & ", " & [FirstName] AS FullName
FROM YourTableName)

The Tag property would then look like this:
Where=FullName,string

 

[tizwaz]

Actually unless there is a better way I've decided to remove the select distinct from my query which created the list box. This means I have several instances of the same name to select but it means I can use the issue id in the where clause of the SQL.

It's not ideal but at least it works.

If there is a better way I'd still be grateful of the advice though

Thanks again