Pass information from a form to a query as criteria

[sesling]

I have created a query that will pull information from our database for the operators. This query will pull in on average 50,000 records. The operators need to refine the search results. I have used the following in the criteria section of the query for the operators to refine the search [Enter the Clients Name:]. However, the operators do not always remember how the clients name appears in the DB and we get several failed queries. To correct this, I created a form with a pick list and a command button. The operator will select the clients name and run the query from the command button. In the query I replaced [Enter the Clients Name:] with the following; forms!form1!tex t1. The selected value in text1 from the form gets passed to the query and the results are returned. This will shrink the results to maybe 2000 records. However, we still need to refine the search even further. One of the fields in the query stores branch numbers. The field in the table is a text field. The operator needs to exclude a certain group of branches from the results. On the form I created a text box for them to enter the branches to exclude. I have them entering
Not In ("59","99","78" ). When this gets passed to the query, Access puts single ' around the text and the query returns 0 records. If the Not In statement is placed directly in the query criteria field we get the proper results. How can I pass this type of information from a form to a query so we can refine the results?

[sesling]

In your command Button code (OnClick event)
You need some code that will modify your SQL.
Then you need to have a temp query to put the new SQL in.
If you put it in the original query then this will be changed for next time (Unpredictable) .

Something along the lines of

Dim strSQL As String, strWhere As String
Dim intWhere As Integer
 
strSQL = CurrentDB.QueryDefs("YourQueryName").SQL
intWhere = InStr(1, strSQL, "WHERE ")
strWhere = "WHERE (([Date]=" & FORMS!ACTIVITY!DATE & ") AND " & _
            "([BRANCH]" & FORMS!ACTIVITY!BRANCH & "))"
strSQL = Left(strSQL,intWhere) & strWhere
CurrentDB.QueryDefs("TempQueryName").SQL = strSQL

You'll have to fill in the gaps. I haven't time to do it all for you, but this is the idea.
Have fun.

I still could not get the query to work the way I wanted. I did find a work around. I changed the text branch column to a value in my query "Val(Branch )". I created four text boxes on the form. I referenced those boxes in the query criteria section. The operators can now enter up to 4 branches to exclude from the query. This has been working for a few days now. I may need to add additonal boxes but this should not be an issue. Thanks for all your assistance.