Query Error!!!

[jbento]

All,
I am getting the error message below:

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? I also placed this question in the Access Queries... section, but the solution there didn't work.

The solution I got there was:
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.

That didn't work.

Please help.


Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[Rolliee]

Jerome,

Let the wiz make your query and then change to the SQL view and copy the SQL to the clipboard and then use that to build your own query. This is by far the easiest way to debug a query.

Rollie