Query Won't Accept Criteria from Form

[Xiaoding]

Hi all,

I have a query, that I have set up to take criteria from a form input. It takes a number, and returns a record based on that number.

Now, working just with the query, I can enter this as the criteria: "Like 0744 or 5222 or 2859", etc., and the query design window will take those multiple numbers, and format them, and run the query.

But, if I use a form to input those numbers, ie "Like [Forms]![frmEquipmentNotFoundWorkOrders]![txtCEInput]", I find it will work if I enter one number in the text box, but if I enter more than one, such as "4226, 4568," or even "4226 or 5222 or 4252", it won't work, no results given.

So, I guess the best result would be, having the query take a comma seperated list from the text box on the form. Is that doable?

[ajetrumpet]

yes it's doable, but form referencing only goes as far as referencing one entity. you shouldn't put more than one criteria in one box. split it up into multiple boxes and build the statement dynamically in code.

putting a comma separated list in one box is almost impossible to use in an sql statement from the query builder anyway.

[NTC]

probably the simplist solution for the novice is to take advantage of the query design having multiple criteria lines which are an OR statement when used.....to implement this you would make as many textboxes on your form as may be needed - let's say 3: then put each on a separate line in your criteria line i.e.

"Like [Forms]![frmEquipmentNotFoundWorkOrders]![txtCEInput1]"
"Like [Forms]![frmEquipmentNotFoundWorkOrders]![txtCEInput2]"
"Like [Forms]![frmEquipmentNotFoundWorkOrders]![txtCEInput3]"

[Xiaoding]

Thnx guys, I went with the many boxes solution, it works for now.

Thanks for the suggestion about learning queries, I just took a course in VBA, but it didn't really get into that, it's my next lesson now!