List Parameter from Text Box Control

[Elysynn]

Greetings,
I'm working on a query that will currently will allow a user to search by either a single client code or by all. I need to modify the parameter so it will accept multiple client codes. If I modify the query directly using In ("CC1","CC2"), the query works as it should. However if I try and tie it into the text box that feeds the query it does not function. Here is what I am attempting to use: In([forms]![P_frmSummaryReport]![txtDIV]). In the control on the form, I have entered "CC1","CC2". What step am I missing?

A drop down or list box really is not a practical solution in this area, as the list of client codes exceeds 1200.

Please let me know if additional information is needed to understand the problem in question.

Thanks in advance for your help!!
-Elysynn

 

[dhookom]

This won't work. You can't grab multiple values like this. You would need to use code to modify the query or build a where clause used to open a report or form. Is your query used as a record source?

There are a couple code samples in the Reports forum FAQs that resolve the multi-select list box issue.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Elysynn]

This query is a base query and is not used directly as a record source. It just seems odd that I can get the result I want when I put the criteria directly into the query, but when I use the text box as the criteria it no longer works. Is there some elementary reason for this that I'm just not getting?

Is the multi-select list box option the only way to get this to work?

Thanks,
Elysynn

 

[dhookom]

Using the form control results in something like:
[blue]In ("""CC1"",""CC2""")[/blue]
which will not match any records unless they contain multiple values with quotes in a single field.

I am not aware of a solution that doesn't use code. I rarely apply dynamic criteria to any saved query. My queries are used as record sources for reports and forms. I build a where clause with code that is used in the OpenForm and OpenReport methods.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Elysynn]

I understand. That makes much more sense now.

I'm trying to conceptualize how you are using the where clause in the OpenForm and OpenReport methods. Could you perhaps provide an example? Is this something in which criteria can be entered? I think I'm getting the idea, I just want to make sure the idea I'm getting actually applies before I get too excited...

Thanks for your help!!
Elysynn

 

[dhookom]

Check this out faq703-3936 to see if you can follow.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.