Syntax error on SQL statement
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"
CurrentDb.QueryDefs("qryExample").SQL = strSQL
DoCmd.OpenForm "frmResults", acNormal
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
If chkAssetNumber Then
strWHERE = strWHERE & "AND s.AssetNumber= " & cboAssetNumber
If chkSerialNumber Then
strWHERE = strWHERE & "AND s.SerialNUmber = " & cboSerialNumber
If chkDescription Then
strWHERE = strWHERE & "AND s.Description = " & cboDescription
If chkManufacturer Then
strWHERE = strWHERE & "AND s.Manufacturer = " & cboManufacturer
If chkLocation Then
strWHERE = strWHERE & "AND s.Location = " & cboLocation
If chkPeriod Then
strWHERE = strWHERE & "AND s.Period = " & cboPeriod
strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
BuildSQLString = True
Any help will be appreciated!
Thanks a lot.