Multiselect Litsboxes and SQL statements

  • Thread starter Thread starter Zelgrath
  • Start date Start date
Z

Zelgrath

I have a form with 4 Multi select list boxes and 1 text
box. I need to construct a query that uses the input from
each of these list boxes and the text box. I have code
that builds a successful SQL statement with the in()
function. Unfortunately I need to use the wildcard
characters in the textbox entry. My problem is this,

If I use in() to build a choice list of the textbox
entries then it matches them exactly instead of paying
attention to wildcards. IE 45550,45330,37*,34* would try
to match each entry exactly.

If I use like then I get bad results from the query
because of the structure of the sql. It is
Where w in(list1Choices) and x in(list2Choices) and y in
(list3Choices) and z in(list4Choices) and like '56660' or
like '377*". This would return the entries I want and the
entries that match like '377*" which I don't want.
 
If I use like then I get bad results from the query
because of the structure of the sql. It is
Where w in(list1Choices) and x in(list2Choices) and y in
(list3Choices) and z in(list4Choices) and like '56660' or
like '377*". This would return the entries I want and the
entries that match like '377*" which I don't want.
Click to expand...

You need to specify the name of the field you're searching with the
textbox, and you need to enclose the OR list in parentheses. Try

Where w in(list1Choices)
and x in(list2Choices)
and y in (list3Choices)
and z in(list4Choices)
and (V like '56660' or V like '377*');

John W. Vinson[MVP]
 
Back
Top