DAO QueryDef and Parameters

[boblarson]

I'm not real good with DAO, and I am building a dynamic SQL string and need to pass 11 parameters to this query. How do I assign the SQL to a querydef object and the parameters? How do you tell it that it has parameters? Do you use something like:

strSQL = "PARAMETERS x1 Bit, x2 Bit, x3 Bit, x4 Bit, x5 Bit, x6 Bit, x7 Bit, x8 Bit, x9 Bit, x10 Bit, x11 Bit;"

Let me know if you need other info as I'm not sure what to supply at the moment.

 

[Rich]

Something like this any help?

Sub CreateQuery()
On Error GoTo Err_CreateQuery

Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT * FROM " & "[" & Me!cboTable & "]" & " WHERE "

If cboFieldName1.Column(1) >= 1 And cboFieldName1.Column(1) <= 7 Then
strSQL = strSQL & Me!cboFieldName1 & " " & Me![cboFirstOperator] & " " & Me![cboDescriptor1] & " "
ElseIf cboFieldName1.Column(1) = 8 Then
strSQL = strSQL & Me!cboFieldName1 & " " & Me![cboFirstOperator] & " #" & Me![cboDescriptor1] & "# "
ElseIf cboFieldName1.Column(1) = 10 Then
strSQL = strSQL & Me!cboFieldName1 & " " & Me![cboFirstOperator] & " """ & Me![cboDescriptor1] & """ "
End If

If Me!cboSecondOperator.Enabled = True Then
strSQL = strSQL & Me![cboLogical]
If cboFieldName2.Column(1) >= 1 And cboFieldName2.Column(1) <= 7 Then
strSQL = strSQL & " " & Me!cboFieldName2 & " " & Me![cboSecondOperator] & " " & Me![cboDescriptor2] & " "
ElseIf cboFieldName2.Column(1) = 8 Then
strSQL = strSQL & Format(" " & Me!cboFieldName2 & " " & Me![cboSecondOperator] & " #" & Me![cboDescriptor2] & "# ", "mm/dd/yyyy")
ElseIf cboFieldName2.Column(1) = 10 Then
strSQL = strSQL & " " & Me!cboFieldName2 & " " & Me![cboSecondOperator] & " """ & Me![cboDescriptor2] & """ "
End If
End If

If Me!cboThirdOperator.Enabled = True Then
strSQL = strSQL & Me![cboLogical2]
If cboFieldName3.Column(1) >= 1 And cboFieldName3.Column(1) <= 7 Then
strSQL = strSQL & " " & Me!cboFieldName3 & " " & Me![cboThirdOperator] & " " & Me![cboDescriptor3] & " "
ElseIf cboFieldName3.Column(1) = 8 Then
strSQL = strSQL & " " & Me!cboFieldName3 & " " & Me![cboThirdOperator] & " #" & Me![cboDescriptor3] & "# "
ElseIf cboFieldName3.Column(1) = 10 Then
strSQL = strSQL & " " & Me!cboFieldName3 & " " & Me![cboThirdOperator] & " """ & Me![cboDescriptor3] & """ "
End If
End If


'MsgBox strSQL

'*** delete the previous query
db.QueryDefs.Delete "qryMyQuery"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If

End Sub

 

[boblarson]

Thanks Rich. Actually, it turned out that my SQL statement, of which I spent a good hour looking over and over at was missing a crucial piece of it (the FROM clause). Once I got it taken care of, the rest worked the way the other developer who had done this app up had designed it.

I'll post the results when I can get to the file again.