Criteria in a form control

[Wadero]

I tried to search for an answer but the results were too broad and some of my words were excluded from the search because they were too common so I was not getting anywhere.

I have 2 questions:

First, I have a form set up where a user selects query criteria from a multi-select list box. My code takes the list box selections and concatenates them into a query criteria string, ie: In ("DOG", "CAT", "RABBIT") which I then store in a textbox.

When I reference this textbox from the criteria in my query [Forms]![SomeForm]![SomeTextBox] it does not work. Am I going about this the wrong way?

Second of all, I would like to change the criteria from In(), to Like * to find all records that contain the words in the list box selections (DOG, CAT, RABBIT...etc). Do I need to set up the criteria as Like "*DOG*" Or Like "*CAT*" Or Like "*RABBIT*" or is there a way to combine Like * with In ()?

Thanks for helping.

 

[meboz]

Could you post the SQL statement that your attempting to execute?

The WHERE clause should look like

WHERE sometable.somefield [forms]![someform]![somecontrol]

 

[Wadero]

Actually I'm not using a SQL statement but an actual query object. I am using VBA to concatenate a string from the listbox to use as the criteria for the query. From there I open a report which is based on that query.

The SQL for that query currently looks like:

WHERE (sometable.somefield) = [forms]![someform]![somecontrol]

but if I remove the = I get a missing operator error.

So it looks like I can't use the criteria in that way from a form control in query object?

What about using In and Like together? I tried:

In(Like "*DOG*", Like "*CAT*", Like "*RABBIT*")

...but I do not get any results from that. Is it even possible?


Thanks again.