Run-time error '3075' on search form

[Jake55923]

Any ideas on why this code will not work when I enter a parameter in the Defect field in my form? I get a "Run-time error '3075' Syntax error in string expression". I borrowed this code from this website & I am not that familiar with it. It does work for any combination of the Machine, Review_By and Operator fields. Any help would be greatly appreciated. Thanks...

Option Compare Database

Private Sub command122_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 * " & _
"FROM [tbl RA Data]"

strWhere = "WHERE"

strOrder = "ORDER BY [tbl RA Data].Date;"

'Set the WHERE clause for the QueryDef if information has been entered into a field on the form

If Not IsNull(Me.[Machine]) Then
strWhere = strWhere & " ([tbl RA Data].[Machine]) = '" & Me.[Machine] & "' AND"
End If

If Not IsNull(Me.[Review_By]) Then
strWhere = strWhere & " ([tbl RA Data].[Review_By]) = '" & Me.[Review_By] & "' AND"
End If

If Not IsNull(Me.[Operator]) Then
strWhere = strWhere & " ([tbl RA Data].[Operator]) Like '*" & Me.[Operator] & "*' AND"
End If

If Not IsNull(Me.[Defect]) Then
strWhere = strWhere & " ([tbl RA Data].[Current Defect]) Like '*" & Me.[Defect] & "*' OR ([tbl RA Data].[Current Prob Descr]) Like '*" & Me.[Defect] & "*' AND"
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("qry sort by")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

'Open the Query
DoCmd.OpenQuery "qry sort by", acViewNormal

End Sub

 

[WayneRyan]

Jake,

I'll bet that you have single-quotes in the defect that you are searching
for. There have been many posts here (I think there samples in the
FAQ or code forums).

If that's the case, let me know.

Wayne

 

[Jake55923]

Wayne thanks for your response, but I can't figure out where I need (or don't need) the single quotes you mentioned. For your information, the line of code that is highlighted when i debug is:

qryDef.SQL = strSQL & " " & strWhere & "" & strOrder


Thanks

 

[WayneRyan]

Jake,

When you encounter the error, does the defect name have a single-quote
in it?

If it does, then you have an easy course of action. The single-quotes show
SQL how to delimit your [Current Defect] field. If it contains a single-quote
then you have

Name = 'O'Reilly'

and the SQL parser thinks it is finished when it finds the SECOND apostrophe.

Check it out and let me know.

Wayne

 

[Jake55923]

Finally figured out that my problem had to do with parentheses.

I changed this:
strWhere = strWhere & " ([tbl RA Data].[Current Defect]) Like '*" & Me.[Defect] & "*' OR ([tbl RA Data].[Current Prob Descr]) Like '*" & Me.[Defect] & "*' AND"

To this:
strWhere = strWhere & " (([tbl RA Data].[Current Defect]) Like '*" & Me.[Defect] & "*' OR ([tbl RA Data].[Current Prob Descr]) Like '*" & Me.[Defect] & "*') AND"

And it worked like a charm! Thanks for the help.