runtime error 3075 in query expression

[ChristianP]

Hi,

I get a runtime error 3075(syntax error (missing operator)
in query expression.

The debugger points at this line of code:
CurrentDb.QueryDefs("qryExample").SQL = strSQL

Extracted from this this sub:
Private Sub cmdFind_Click()
Dim strSQL As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

MsgBox strSQL

CurrentDb.QueryDefs("qryExample").SQL = strSQL

DoCmd.OpenForm "frmResults", acNormal

End Sub

I have also built a SQL statement were the cause of
troubles might originate from:
Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.*"

strFROM = "tblObjects s "
If chkOwnerID Then
strFROM = strFROM & "INNER JOIN tblOwners i " & _
"ON s.OwnerID = i.OwnerID"
strWHERE = " AND i.OwnerID= " & cboOwnerID
End If

If chkAssetNumber Then
strWHERE = strWHERE & "AND s.AssetNumber= " &
cboAssetNumber
End If

If chkSerialNumber Then
strWHERE = strWHERE & "AND s.SerialNUmber = " &
cboSerialNumber
End If

If chkDescription Then
strWHERE = strWHERE & "AND s.Description = " &
cboDescription
End If

If chkManufacturer Then
strWHERE = strWHERE & "AND s.Manufacturer = " &
cboManufacturer
End If

If chkLocation Then
strWHERE = strWHERE & "AND s.Location = " & cboLocation
End If

If chkPeriod Then
strWHERE = strWHERE & "AND s.Period = " & cboPeriod
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " &
Mid$(strWHERE, 6)

BuildSQLString = True

End Function

The rest seems to be working fine, so I am really confused
and would appreciate any help with this.
Many Thanks,
ChristianP

 

[Douglas J. Steele]

See whether it works any better if you add the AS keyword:

strFROM = "tblObjects AS s "
If chkOwnerID Then
strFROM = strFROM & "INNER JOIN tblOwners AS i "

 

[Dirk Goldgar]

Hi,

I get a runtime error 3075(syntax error (missing operator)
in query expression.

The debugger points at this line of code:
CurrentDb.QueryDefs("qryExample").SQL = strSQL

Extracted from this this sub:
Private Sub cmdFind_Click()
Dim strSQL As String

If Not EntriesValid Then Exit Sub

If Not BuildSQLString(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

MsgBox strSQL

CurrentDb.QueryDefs("qryExample").SQL = strSQL

DoCmd.OpenForm "frmResults", acNormal

End Sub

I have also built a SQL statement were the cause of
troubles might originate from:
Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.*"

strFROM = "tblObjects s "
If chkOwnerID Then
strFROM = strFROM & "INNER JOIN tblOwners i " & _
"ON s.OwnerID = i.OwnerID"
strWHERE = " AND i.OwnerID= " & cboOwnerID
End If

If chkAssetNumber Then
strWHERE = strWHERE & "AND s.AssetNumber= " &
cboAssetNumber
End If

If chkSerialNumber Then
strWHERE = strWHERE & "AND s.SerialNUmber = " &
cboSerialNumber
End If

If chkDescription Then
strWHERE = strWHERE & "AND s.Description = " &
cboDescription
End If

If chkManufacturer Then
strWHERE = strWHERE & "AND s.Manufacturer = " &
cboManufacturer
End If

If chkLocation Then
strWHERE = strWHERE & "AND s.Location = " & cboLocation
End If

If chkPeriod Then
strWHERE = strWHERE & "AND s.Period = " & cboPeriod
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " &
Mid$(strWHERE, 6)

BuildSQLString = True

End Function

The rest seems to be working fine, so I am really confused
and would appreciate any help with this.
Many Thanks,
ChristianP

I see you have a MsgBox statement to display the SQL statement.that you
built in strSQL. What is that statement? It would be best if you can
copy and paste the statement, as the problem is likely to be a missing
space between words.

 

[John Spencer (MVP)]

It looks to me as if your SQL string is being built without spaces in required places.

Try using a msgbox or debug.print statement to check out what the SQL you are
generating looks like. When I did a "deskcheck" of your code it seemed to me
that you would end up without a space between SELECT S* and the FROM clause.
And if ChkOwnerID was true another missing space between the end of the join
statement and the beginning of the WHERE clause.

SELECT S.*FROM ...