Having some troubles making an IN statement do what I want. Here is what I am looking for.
I have a series or queries that the user runs from a form. I want to provide the ability to filter by the customer type. There are 5 customer types, each one is related to one or more customer numbers. The user knows this by type not by customer number.
I want a form where the user can click checkboxes of the customer types to filter the query by but is not required to choose any of them (There are customers that do not fall into any type).
I attempted to make a query with something like In(934,944) as the criteria for customer number. This works fine if I type it directly in the query, however, I cannot get it to feed from the form. I was trying to use a text box that is generated by the click event of the check boxes. The text box generates a proper criteria and If I cut and paste it into the query all is fine but it won't work directly off the form.
I am also willing to do this as a temp table and a IN (select...) but I can't get that to work if no customer type is chosen.
Thanks for the thought, that is basically what I am doing. The problem I am having is the number of customer numbers that need to be filtered on changes depending on the customer type. Entering what you wrote directly in the query would work fine if there were always two customer numbers, but that isn't always the case.
I tried using IN(Forms!frmReport.txtCriteria) but that doesn't work as it should, especially if there are multiple criteria in the text box like 992,993