some problems with dynamic query... (1 Viewer)

[pjustin1]

Hi, I tried to create a dynamic query where the user may search by any combinations of the 3 input fields. It's still not fully completed but i have encounter some problems.. i wonder can anyone help me out.. thanks...

"****" is where the problems lie

Dim dbs As Database, qdf As QueryDef, strSQL As String, dynamic As String
Set dbs = CurrentDb
Dim cond1 As Variant, cond2 As Variant, cond3 As Variant
strSQL = "SELECT * FROM CUSTOMER where"
cond1 = Me.Text0
cond2 = Me.Text2
cond3 = Me.Text7
On Error Resume Next
dbs.QueryDefs.Delete ("test")
On Error GoTo 0


If IsNull(cond1) = False Then
dynamic = " [CUSTOMER NAME] ='" + Me.Text0 + "'"
End If
If IsNull(cond1) = False And IsNull(cond2) = False Then
dynamic = dynamic + " And"
End If
If IsNull(cond2) = False Then
dynamic = dynamic + " [RATING] ='" + cond2 + "'"
End If
If Not (cond3 = 0) Then

**** "NO OF HOLES" is integer, a type mismatch error is given.. what should be the correct expression?
dynamic = dynamic & " AND " & ("[NO OF HOLES] = " + cond3)
End If

strSQL = strSQL + dynamic
Set qdf = dbs.CreateQueryDef("test", strSQL)
DoCmd.OpenQuery ("test")

**** Me.Refresh doesn't work here, it cannot refresh the subform... I have tried out with other fixed queries, it's working fine.. but not with this...
Me.Refresh

btw, if I have 6 input fields.. what is the best soln to handle the "AND" command in the SQL...?
Thanks
Justin

 

[Pat Hartman]

I don't see a specific problem but here are some suggestions:
1. Define all your variables with Dim statements. Add an Option Explicit line to your module and set this as the default for EVERY database. This will make sure that typos aren't causing the problem.
2. The ampersand (&) is the standard concatenation character. The plus (+) will serve this function in some cases but you must be aware of the differences. Read the help entries for BOTH operators so that you understand when and why to use the +. Otherwise, stick exclusively to the &.
3. Print the contents of strSQL in the debug window after it is built. If you see the error, fix it. Otherwise, copy the string and paste it into the SQL view of the query builder and test it there.