Type Mismatch error with "Like"

[LaurieW]

I have code on a button in the On Click Event that fills a report title and gets data from a table. I get a type mismatch error on this line and believe it is having a problem with the "Like" operator. I took this code directly from the SQL statement from a query...why does it not work? How do I use something similar to "Like" in this statement"

DoCmd.OpenReport stDocName, acPreview, , "((tblPayee.FirstName) Like " * " & [Forms]![frmReportMenu]![txtSearchFor] & " * "))"

Here is all my code, in case you want a look. I have done this same thing before but only using things like = and > and <...never trying "Like". Hope someone can help. Thanks, Laurie

Private Sub cmdTenantSearch_Click()
On Error GoTo Err_cmdTenantSearch_Click

Dim stDocName As String

Me.Controls("txtSearchFilter") = Me.Controls("cboSearchFilter")
stDocName = "rptTenantSearch"
If cboSearchFilter = "Address" Then
Me.Controls("txtReportTitle") = "Tenant Search For " & [Forms]![frmReportMenu]![txtSearchFor] & " In Address"
' DoCmd.OpenReport stDocName, acPreview, , "(((tblPayee.Address1) Like " * " & [Forms]![frmReportMenu]![txtSearchFor] & " * ")) OR (((tblPayee.Address2) Like " * " & [Forms]![frmReportMenu]![txtSearchFor] & " * "))"
ElseIf cboSearchFilter = "First Name" Then
Me.Controls("txtReportTitle") = "Tenant Search For " & [Forms]![frmReportMenu]![txtSearchFor] & " In First Name"
DoCmd.OpenReport stDocName, acPreview, , "((tblPayee.FirstName) = [Forms]![frmReportMenu]![txtSearchFor])"
' DoCmd.OpenReport stDocName, acPreview, , "((tblPayee.FirstName) Like " * " & [Forms]![frmReportMenu]![txtSearchFor] & " * "))"
ElseIf cboSearchFilter = "Last Name" Then
Me.Controls("txtReportTitle") = "Tenant Search For " & [Forms]![frmReportMenu]![txtSearchFor] & " In Last Name"
' DoCmd.OpenReport stDocName, acPreview, , "((tblPayee.LastName) Like " * " & [Forms]![frmReportMenu]![txtSearchFor] & " * "))"
End If

Exit_cmdTenantSearch_Click:
Exit Sub

Err_cmdTenantSearch_Click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox "Error No: " & Err.Description & "; Description: " & Err.Description
Resume Exit_cmdTenantSearch_Click
End If
End Sub

 

[LaurieW]

Never mind, got it!

I looked in SQL Books Online and found I needed to use the % character instead of *, so this works:

DoCmd.OpenReport stDocName, acPreview, , "((tblPayee.FirstName) Like '%' & [Forms]![frmReportMenu]![txtSearchFor]& '%')"

Thanks anyway!
Laurie

 

[tkpstock]

Glad we could help!