CODEWITHSUNDEEP
vbams-accessms-access-2016
Deke
Deke

Reputation: 495

Type Mismatch with Like "*"

I get a type mismatch where it's trying to set my Like "*"

I set Dim to a String and this field is all short text.

In an Access query it works.

I tried it this way (preferred)

Private Sub AppendFilter_Change()
    Dim UserF As String
    Dim DateF As String
    
    UserF = Me.UserFilterCombo
    DateF = Me.AppendFilter
    
    If UserF = "" Then
       UserF = "Like "*"" '<---Type Mismatch??
    Else
    End If
    
    Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
    & "FROM tblTasks " _
    & "WHERE (((tblTasks.Owner)='" & UserF & "') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
    & "ORDER BY tblTasks.Owner, tblTasks.DueDate;"
    
    Me.TasksLst.Requery
End Sub

I tried it this way (not my favorite)

Private Sub AppendFilter_Change()
Dim UserF As String
Dim DateF As String

UserF = Me.UserFilterCombo.Value
DateF = Me.AppendFilter

If UserF = "" Then
Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
& "FROM tblTasks " _
& "WHERE (((tblTasks.Owner) Like " * ") AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like " * ") AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner) Like " * ") AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
& "ORDER BY tblTasks.Owner, tblTasks.DueDate;"

Else
Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
& "FROM tblTasks " _
& "WHERE (((tblTasks.Owner)='" & UserF & "') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
& "ORDER BY tblTasks.Owner, tblTasks.DueDate;"

End If

Me.TasksLst.Requery
End Sub

Upvotes: 0

Views: 130

Answers (1)

Jon vB
Jon vB

Reputation: 71

I would have your logic so that the Where clause doesn't mention the user at all if the user hasn't specified a user (and same for date range - unless you want to put a default date range).

something like the below ... I haven't passed SQL statements to the rowsource, so I don't know how it uses the LIKE statement ... in SQL it would be single quote and % sign .... based on you using the *, this code uses single quote and star.

Private Sub AppendFilter_Change()
    Dim UserF As String
    Dim DateF As String
    
    UserF = Me.UserFilterCombo
    DateF = Me.AppendFilter
    
    If UserF = "" Then
       UserF = ""
    Else
        UserF = " AND (tblTasks.Owner) LIKE '*" & UserF & "*'"
    End If
    'repeat for DateF
    
    Me.TasksLst.RowSource = "SELECT tblTasks.ID, tblTasks.Owner, tblTasks.[Task Name], tblTasks.Priority, tblTasks.Company, tblTasks.Status, tblTasks.Notes, tblTasks.DueDate, tblTasks.StartDate, tblTasks.DateCompleted, tblTasks.DateCreated, tblTasks.[Need Help], tblTasks.Assigned " _
    & "FROM tblTasks " _
    & "WHERE (( ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='Not Started') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) OR (((tblTasks.Owner)='" & UserF & "') AND ((tblTasks.Status)='In Progress') AND " & DateF & " AND ((tblTasks.[Recurring Event])=False)) " _
    & UserF & DateF _
    & " ORDER BY tblTasks.Owner, tblTasks.DueDate;"
    
    Me.TasksLst.Requery
End Sub

Upvotes: 2

Related Questions