Where Statement Contained in Text Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to run a query using a Where statement that I've created in a
text box. The reason: I want users to have more dynamic search criteria.
For example, combo box 1 may list Last Name, First Name, City, State, etc.
When the user selects it, the next combo box provides them the values for
that selection. The users then makes that selection.

A Where statement based on these combo boxes is then created in a separate
text box. I know how to create this in the text box, but how do I have the
query use this as the criteria?

Thanks for your help.
Bernie
 
The easiest solution will be to leave the WHERE clause of of the query, and
just assign the string you built to the Filter of the form, like this:
Me.Filter = strWhere
Me.FilterOn = True
The form is then filtered as if you used this WHERE clause in its query.

If you need something more powerful, you could add the WHERE clause you
created to the other parts of the query statement, and then assign the whole
SQL statement to the RecordSource of the Form

If you switch your query to SQL View (View menu, in query design), you can
see that the WHERE clause goes after the SELECT and FROM clauses, and before
the ORDER BY clause. Just copy the whole of the statement before the WHERE
clause into a string, and anything after the WHERE clause into another
string. The code will look like something this:
Dim strSql As String
Const strcStub = "SELECT Table1.* FROM Table1 "
Const strcTail = " ORDER BY Table1.Field1;"

If IsNull(Me.Text0) Then
strSql = strcStub & strcTail
Else
strSql = strcStub & "WHERE " & strcTail
End If
Me.RecordSource = strSql

In practice, I would not trust an end use to get the WHERE clause right, but
I use both those techniques in search forms at different times.
 
Thank you very much...I used your first suggested solution and it works
great! I struggled with this and tried finding the answer on my own.
Thanks for the help!
Bernie
 
Back
Top