Query Error

[jbento]

I hope someone can help me with this. I am getting the following error for the code further down:

Syntax error (missing operator) in query expression 'tbl_sub1.MainID = tbl_sub2.MainID FROM tbl_test INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID'.

Here is the code I am using:

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()


'Constant Select statement for the Query definition
strSQL = "SELECT tbl_test.MainID,"
strSQL = strSQL & "tbl_test.field1, tbl_test.field2, tbl_test.field3,"
strSQL = strSQL & "tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2,"
strSQL = strSQL & "tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2 "
strSQL = strSQL & "FROM (tbl_test INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID) INNER JOIN tbl_sub2 ON tbl_sub1.MainID = tbl_sub2.MainID "

'strSQL = "SELECT tbl_test.* " ', tbl_sub1.*, tbl_sub2.*"
strSQL = strSQL & "FROM tbl_test "
'strSQL = strSQL & "INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID)"
'strSQL = strSQL & "INNER JOIN tbl_sub2 ON tbl_test.MainID = tbl_sub2.MainID)"
strWhere = "WHERE"



strOrder = "ORDER BY tbl_test.MainID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.field1) Then '<--If the textbox field1 contains no data THEN do nothing
strWhere = strWhere & &quot; (tbl_test.field1) Like '*&quot; & Me.field1 & &quot;*' AND&quot; '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.field2) Then
strWhere = strWhere & &quot; (tbl_test.field2) Like '*&quot; & Me.field2 & &quot;*' AND&quot;
End If

If Not IsNull(Me.field3) Then
strWhere = strWhere & &quot; (tbl_test.field3) Like '*&quot; & Me.field3 & &quot;*' AND&quot;
End If

If Not IsNull(Me.fieldA1) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA1) Like '*&quot; & Me.fieldA1 & &quot;*' AND&quot;
strSQL = strSQL & &quot;INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID &quot;
End If

If Not IsNull(Me.fieldA2) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA2) Like '*&quot; & Me.fieldA2 & &quot;*' AND&quot;
strSQL = strSQL & &quot;INNER JOIN tbl_sub2 ON tbl_test.MainID = tbl_sub2.MainID &quot;
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs(&quot;qry_test&quot
qryDef.SQL = strSQL & &quot; &quot; & strWhere & &quot;&quot; & strOrder

'Open the Query
DoCmd.OpenReport &quot;rpt_test&quot;, acViewPreview, &quot;&quot;, &quot;&quot;, acNormal

End Sub

If I go to the query itself it works fine, but if I run the code above from a command button it doesn't work and gives me that error above.

What is wrong with that code?

Please help.

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[r937]

Syntax error (missing operator) in query expression 'tbl_sub1.MainID = tbl_sub2.MainID FROM tbl_test INNER JOIN tbl_sub1 ON tbl_test.MainID = tbl_sub1.MainID'.

you have FROM after a join

rudy

http://r937.com/

SQL Consulting