OK, here is a simple example of using the Form method:
1. First, create a query which does everything you want, except for this dynamic query parameter. So, select all the fields you want, doing any caclulations, joins, other criteria that doesn't change. Let's name this Query1.
2. Then, create a query and name it Query2. In this query, select Query1, click * to add/display all fields, and enter an example of the Criteria you want to build, like excluding two specific cities. Then, change to SQL View, and copy and paste the SQL code to a Word or text document for reference (this is what the SQL code that we want to build in VBA needs to look like).
3. Now, create an Unbound Form, and add two objects to it: a TextBox named "txtExclude" and a command button named "cmdOpenQuery".
4. Set the "On Click" property of the command button to run this VBA code:
Code:
Private Sub cmdOpenQuery_Click()
Dim strSQL As String
' Build SQL string for second query (should be in same format of SQL code you copied from your Query2 example)
strSQL = "SELECT * FROM Query1 WHERE City Not in (" & Me.txtExclude & ");"
' Temporary message box to confirm code written correctly
MsgBox strSQL
' Assign SQL code to Query2
CurrentDb.QueryDefs("Query2").sql = strSQL
' Open Query2
DoCmd.OpenQuery "Query2"
End Sub
5. Now, just enter the cities you wish to exclude in the Text Box on the form, surrounded by double-quotes and separated by commas, i.e.
Code:
"Austin","Buffalo","Denver"
and click the command button. It should open up the query results, excluding the cities you listed in the Text Box.
If you wanted to get a little fancier, you could list the cities you want in separate fields, or select them from a list, and then loop through the list and build the string you need, adding in the double-quotes and commas where needed. It is really a matter of personal choice, how you would like them to enter/select the cities to exclude.