Use listbox values as input to SQL query

[ExcessionOCP]

Hi,

I have a listbox that returns some data. I'd like my users to be able to select items from that list box and then use those selected items in a SQL query as a WHERE clause. What's the best way of going about this?

For example, I have a table that contains timesheet data. I'd like my users to be able to select a few usernames, then use those usernames as a filter to only return the timesheets belonging to those users.

Thanks in advance!

[Welshgasman]

Use the IN() clause and concatenate the listbox values.

[ExcessionOCP]

Thank you! That pointed me in exactly the direction that I needed

[ExcessionOCP]

Darn! So close. Some of the selected values may be present or may not be present (there's also a date filter being used and not all usernames will appear in every selected date range) so the IN() clause returns nothing if they're not present. Do you have any suggestions?

[Welshgasman]

That is what you want surely?, else no point selecting items in the listbox, regardless of any other criteria?

If a bunch of employees are selected for last month, and I only joined this month, then despite my being in the IN list, I am not going to appear as I do not have any timesheet for that period.

[ano]

did you fill your listbox with data from the table? to avoid wrong layout
did you setup your database correctly? meaning text (name) is only once visible.
did you translate the name into the linking #?

[madpiet]

Darn! So close. Some of the selected values may be present or may not be present (there's also a date filter being used and not all usernames will appear in every selected date range) so the IN() clause returns nothing if they're not present. Do you have any suggestions?

It's hard to tell from your posts up to now, but you can't just create a query and have this work. If you're using a form or report to filter the query, it's infinitely easier. Otherwise, you have to modify the SQL property of your query.

So then you do the standard looping through the ItemsSelected collection of your multi-select listbox. Pretty sure Dev Ashish wrote an article on how to do that in like 1997. The gist of it is something like

dim varItem as variant
dim strFilter as string
dim lbx as listbox

set lbx = me.controls("MyListboxName")
for each varItem in lbx.ItemsSelected
strFilter = strFilter & "'" & varItem & "'," '-- wrap in single quotes, append a comma
next varItem

strSQL = "SELECT * FROM MyTable WHERE strField IN (" & strFilter & ")"

Currentdb.Querydefs("MyQuery").sql = strSQL

then you do whatever you're gonna do with the query... open it, base a form or report on it...

[ExcessionOCP]

Thanks for everyone's help here. I changed the workflow slightly by prefiltering the records based on the selected dates, then using the prefiltered data set as the source for the user selection, that way the users were always going to be present in the data set and so Welshgasman's plan worked perfectly. Thanks also to madpiet for his assistance.