List Box Query Help (1 Viewer)

[Acropolis]

Hi,

I am trying to create a query that shows data based on the selection of engineers selected in a multi select list box and date range.

The query works fine with just the date range selected, but when I try and filter the engineers to those that i want i am having problems.

I have a multi select list box, and when i press a button, it goes through each selected and create a string of the ID's i have selected, IE 1, 3, 65, 45 etc.

I have then created a temp var that shows as the above, the plan was to put a criteria in the query of

 Criteria: In ([TempVars]![tmpIDS])

IE

 Criteria: In (1, 43, 23, 23)

When typed in as the second example, works fine, however doesn't work with the first using the temp var. I have tried setting a parameter to interger but this still doesn't work.

I have put the string into a text box in the main form and referenced that and it doesn't work.

I am hitting a brick wall now and can't think how to get past it, should be simple but am struggling.

Anyone have any idea's?

 

[Minty]

The normal way to do this is to loop through the selected records and dynamically build the IN clause as a SQL statement then use that. Your temp var will be a string, not a list of Integers which is what the IN statement is expecting.

You may get somewhere with a EVAL statement. https://msdn.microsoft.com/en-us/library/office/aa172212(v=office.11).aspx look about 3 sections down

 

[Ranman256]

Multi list boxes are a pain because they must be programmed with VB.
To avoid this, don't use multi list, instead, use a single list but add a 'picked table'.
This table holds the selections the user picked by double clicking the list.
The double click fires an append query and adds the item to the table.

Then to use the picks,join the picked table to the data table. No programming, simple query.
In the form, you can also add a Delete item using a delete query to remove a bad item.

 

[Acropolis]

Thanks for the reply, seems it's not as easy as I thought initially then, don't feel quite so stupid now lol

I found a work around in the end. I made the SQL query up in the code, assign this to a temp var, and open the report (which is the end result). The report has no record source, and I put an "On Open" event in the report, which sets the Record Source to the temp var, and everything works beautifully now.

Thanks for the replies, some interesting ideas there that i will look into for some other things.