Creating a searchable field using the "In" statement criteria

[StryderLlama]

I don't know if this is possible and I have spent a week searching for an answer. I have a form related to a query that is looking at a massive amount of data. I want the form to be very user friendly so I have designed it so that the user can search for several numbers in a query at once. I want to use the In() method on the query and in the form just have the user type in the field something like the following.

'123456','654321','987654','456789'

That would then in turn be used in the query like this:

In('123456','654321','987654','456789')

I have tried several different ways to make this work. I had this same issue with the Like method on a criteria field but someone here helped me solve that one by concatenating the statement.

However, concatenation does not seem to work for what I am doing here. I have tried to create a function in VBA to do this as well but it does not work either.

Any ideas on how to make this work or is it even possible?

 

[missinglinq]

If these are truly numbers, shouldn't it be


In(123456,654321,987654,456789)

without the single quotes?

 

[Brianwarnock]

I think that the real answer to your question is that it cannot be done, it was discussed at length by some of the experts on here, but I cannot find it which is a pity as I think Pat Hartman had a work around.

Brian

 

[Brianwarnock]

Tracked this thread down, hope it helps

http://www.access-programmers.co.uk/forums/showthread.php?t=96629

Brian

 

[pbaldy]

I'm with Brian; I typically do this type of thing:

http://www.baldyweb.com/multiselect.htm

 

[StryderLlama]

Thank you all so much. This has solved my problem. My entire team will be able to utilyze this and it will make some of our forms much simpler for our department. Thanks again. There is a great wealth of knowledge here.