Pass multiple values to query

[Booter]

Hello,

I have a form where I will allow the user to enter various parameters, which will then be passed to a query. One of these paramaters (LocationID) contains values from 1 to well over 1000. The user of this database would like to enter in a box multiple LocationID's and then press the search key which would generate a report based on the query. The query receives the values from the form. Is there anyway for me to do this.

E.g. the user would type in (without the brackets) "24, 89, 243, 688, 699"

How can I accomplish this so that the above values are passed to the query as parameters.

 

[Mile-O]

Use a multiselect listbox and then build the query.

A possible search term might be listbox QueryDef

 

[Booter]

I realize that you can use multiselect listbox for this problem but I would rather have the option of typing in the values into a text box separated be either a comma or space, etc. Is there no way to do this?

 

[lobodava]

I would suggest the following SQL string:

SELECT ... FROM ... WHERE LocationID In ([MyParameter])

where MyParameter can accept a single value and several comma separated values as well.

But what is going on when you leave the textbox empty?
IMHO it is better to make up SQL string dynamically - it is more flexible.

 

[Booter]

Okay,

Does MyParameter have to be a certain data type? Right now, it's just a text box on a form where the user enters in the LocationID's and it's giving us an error when the query runs.

Here is the WHERE portion of the query in SQL view

WHERE (((Location.LocationID) IN [Forms]![SearchForm]![LocationID]))

If we hard code the query like below it works:

WHERE (((Location.LocationID) IN (1, 22)))

What do you think?

 

[john471]

I suspect you are having a data type problem. I suspect your (erroring) code is evaluating to :-

WHERE (((Location.LocationID) IN ("1, 22")))

instead of

WHERE (((Location.LocationID) IN (1, 22)))

but I don't know how to remedy that, and don't have time to work on it right now. Perhaps someone else can shed more light ????