Search that creates a dynamic query

[Christal]

I'm trying to create a dynamic query that is created when the search button
is clicked. I got it to work to a certain point before, but now I'm stuck.
Its supposed to search from multiple criteria, and pull up any records that
match. Here is the code:

Dim db As Database
Dim QD As QueryDef
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

Set db = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from customers, resume "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
.SetFocus
'This is the function that actually builds
'the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
dbText, .Text)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl


MsgBox "Select * from contacts, resume " & (" where " + Mid(where, 6) &
";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from contacts, resume " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

 

[Daryl S]

Christal -

You need to change your last three lines to the following four lines:

strSQL = strSQL & sWhereClause & ";"
MsgBox strSQL
Set QD = db.CreateQueryDef("Dynamic_Query", strSQL)
DoCmd.OpenQuery "Dynamic_Query"

For other help, please tell us what errors you are getting when you compile
and/or run the code. Note that you ahve created a new query with this code,
so if you run it more than once, you will need to delete the query called
"Dynamic_Query" before you re-create it the next time.