help me please with this code? (1 Viewer)

[icemonster]

is there something wrong with this? am getting a mismatch error.

Function setVisitDueList()
'set starting sql statement
        strStartSql2 = "SELECT qryVisitListVBA2.ID, qryVisitListVBA2.SupervisoryVisitID, qryVisitListVBA2.[Homemaker Name], " _
                       & "qryVisitListVBA2.HireDate, qryVisitListVBA2.InitialVisit, qryVisitListVBA2.SupervisoryVisitDate, " _
                       & "qryVisitListVBA2.ClientID, qryVisitListVBA2.TypeofHomemaker, qryVisitListVBA2.NextVisitOn, " _
                       & "qryVisitListVBA2.VisitDate, qryVisitListVBA2.supervisor FROM qryVisitListVBA2 "
        [COLOR="Red"]strStartSql2 = "WHERE (((qryVisitListVBA2.supervisor) Like " * " & [Forms]![frmSupervisor]![Search3] & " * "));"[/COLOR]

strWhereSql2 = ""
    
If Not IsNull(Me.txtStartDate2) And Not IsNull(Me.txtEndDate2) Then
    'read the dates selected in the variables
    dtStartDate2 = Me.txtStartDate2
    dtEndDate2 = Me.txtEndDate2
    If strWhereSql2 = "" Then
        strWhereSql2 = " WHERE VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    Else
        strWhereSql2 = strWhereSql2 & "AND VisitDate Between #" & dtStartDate2 & "# " _
                    & "And #" & dtEndDate2 & "# "
    End If
End If

strSortOrderSql2 = " ORDER BY qryVisitListVBA2.VisitDate;"

strSQL2 = strStartSql2 & strWhereSql2 & strSortOrderSql2
With Me.lstSupervisoryVisit
    .RowSource = strSQL2
    .Value = Null
End With

End Function

the red highlight indicates where.

 

[KeithG]

try this

strStartSql2 = "WHERE (((qryVisitListVBA2.supervisor) Like ' * " & [Forms]![frmSupervisor]![Search3] & " * '));"

 

[icemonster]

i did that, but now, it's returning nothing, isnt like supposed to be a variant type so that no criteria was made, it would return all the records?

 

[MarkK]

Remove the spaces from the

LIKE ' * "

so you have

LIKE '*"

If those spaces are present they are included in the pattern matching attempt, and no records match.

 

[icemonster]

woot made it work. thanks