SQL Error

[jbento]

All,
I am getting the following error with the code below:

Run-time error '3129';

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE',

The code I am using that is getting this error is below:

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, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2" & _
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"


strWhere = "WHERE (((tbl_sub1.fieldA1)"

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;
End If

If Not IsNull(Me.fieldA2) Then
strWhere = strWhere & &quot; (tbl_sub1.fieldA2) Like '*&quot; & Me.fieldA2 & &quot;*' AND&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

Can someone please help,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[r937]

i don't do whatever language that is (asp? vb?) but it looks like there is a space missing in front of the keyword FROM

also, your logic is more complex than it needs to be, especially the &quot;remove the last AND&quot; part

just start the WHERE clause like this --

WHERE 1=1

then you can add additional conditions, each with a leading AND (rather than a trailing AND)

or, if no conditions are added, the query will get all rows

rudy

http://r937.com/

SQL Consulting

 

[woja]

Firstly take a look at:

strSQL = "SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2" & _
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"

Since the first line ends in &quot;[tt]_[/tt]&quot;, VB considers the next line to be a continuation of the expression on the first line. Therefore it attempts to concatentate [tt]&quot;SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2&quot;[/tt] with [tt]strSQL = &quot;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&quot;[/tt]. This second expression VB treats as a comparsion of [tt]strSQL[/tt] and the string. Since [tt]strSQL[/tt] is empty, the comparsion is [tt]False[/tt].

Therefore, [tt]strSQL[/tt] is set to [tt]&quot;SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2False&quot;[/tt]

Secondly, have a look at the position of the [tt]AND[/tt] in the strings added to [tt]strWhere[/tt] in the [tt]If[/tt] statements:

strWhere = "WHERE (((tbl_sub1.fieldA1)"
If Not IsNull(Me.field1) Then
   strWhere = strWhere & " (tbl_test.field1) Like '*" & Me.field1 & "*'  AND"
End If

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

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

If Not IsNull(Me.fieldA1) Then
   strWhere = strWhere & " (tbl_sub1.fieldA1) Like '*" & Me.fieldA1 & "*'  AND"
End If
If Not IsNull(Me.fieldA2) Then
   strWhere = strWhere & " (tbl_sub1.fieldA2) Like '*" & Me.fieldA2 & "*'  AND"
End If

Assuming that, say, [tt]Me.field3[/tt] is not [tt]Null[/tt] (and has the value &quot;[tt]test[/tt]&quot;, then [tt]strWhere[/tt] will become:

WHERE (((tbl_sub1.fieldA1) (tbl_sub1.field3)(tbl_test.field2) Like '*test*' AND[code]
This is clearly an invalid SQL [tt]WHERE[/tt] clause, even when the final [tt]AND[/tt] is removed.

The code should be something like:
[code]
strWhere = "WHERE (((tbl_sub1.fieldA1)"
If Not IsNull(Me.field3) Then
   strWhere = strWhere & " AND (tbl_test.field3 Like '*" & Me.field3 & "*')"
End If

And yout then don't need to &quot;get rid of the last [tt]AND[/tt]&quot;.

Hope this helps.


[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[woja]

Whoops:

[tt]WHERE (((tbl_sub1.fieldA1) (tbl_sub1.field3)(tbl_test.field2) Like '*test*' AND
[/tt]

should read:

[tt]WHERE (((tbl_sub1.fieldA1) (tbl_test.field3) Like '*test*' AND
[/tt]

Also:

You need to make sure you close all the &quot;[tt]([/tt]&quot;'s at the end of the [tt]WHERE[/tt] statement.

[tt]________________________________________________________________
Roger
Life is a game of cards in which the deck contains only jokers.[/tt]

 

[jbento]

I'm sorry but I'm not that great with SQL, and I have been trying to put the puzzle together with all the responses and I'm not getting anywhere fast

Can someone please put the code together with the way it should look?

When I run the actual query it works perfect. I look at the SQL view of the query and I get this:

SELECT tbl_test.MainID, tbl_test.field1, tbl_test.field2, tbl_test.field3, tbl_sub1.MainID, tbl_sub1.fieldA1, tbl_sub1.fieldA2, tbl_sub2.MainID, tbl_sub2.fieldB1, tbl_sub2.fieldB2
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
WHERE (((tbl_sub1.fieldA1) Like '*john*'))
ORDER BY tbl_test.MainID;

That works great when running directly from the query, but when I try to apply that to the code from my first thread I get hosed.

Somebody please help me get the correct built in.

I'm sorry to be a pain, but I really need help.

Thanks in advance,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!