Syntax error on SQL statement
Hi,
This is all new to me and hope that I can explain my problem clearly.
I have been going through the Access 2000 vba book. So far so good. But when trying to apply some of the code to my database, I got the runtime error 3075, syntax error in query expression (missing operator). The debugger points to the following bold line of code in the following 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 created a sql query which will return the results of the search criteria selected by the users. It works fine except for the line above.
Perhaps this is coming from the following function?:
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
Any help will be appreciated!
Thanks a lot.
Chris
|
