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

Take a look at all of your SQL statements in your function. If ANY of them pass text and not numbers, then change this

"[MyTextField] = " & cboMyTextField

to this

"[MyTextField] = '" & cboMyTextField & "'"

In other words, you must delimit text variables (i.e. surround them) with single quotes. Numbers get nothing, and dates get # signs.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division