New to VBA need help

[rwrig]

Private Sub Search_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.AssignedTo) Then
strWhere = strWhere & "([AssignedTo] Like ""*" & Me.AssignedTo & "*"")"
End If
If Not IsNull(Me.OpenedBy) Then
strWhere = strWhere & "([OpenedBy] Like ""*" & Me.OpenedBy & "*"")"
End If
If Not IsNull(Me.Status) Then
strWhere = strWhere & "([Status] Like ""*" & Me.Status & "*"")"
End If
If Not IsNull(Me.Category) Then
strWhere = strWhere & "([Category] Like ""*" & Me.Category & "*"")"
End If
If Not IsNull(Me.Priority) Then
strWhere = strWhere & "([Priority] Like ""*" & Me.Priority & "*"")"
End If
If Not IsNull(Me.OpenedDateFrom) Then
strWhere = strWhere & "([EnteredOn] >= " & Format(Me.OpenedDateFrom, conJetDate) & ") AND "
End If
If Not IsNull(Me.DueDateFrom) Then
strWhere = strWhere & "([EnteredOn] <= " & Format(Me.DueDateFrom, conJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
End If
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub


This is a code I found online and alter specifically for my database, my problem is when I click search nothing happens.

Can someone tell me what I'm doing wrong, I have drop down boxes for each of the categories I created Like clauses for. So after the filters are applied I will like for the records in the table below the form, that I have in the form filter to filter on those records.

Any help will be appreciated.

 

[CJ_London]

your code is very difficult to read, in future please use the code tags and ensure your code is properly indented

strWhere = strWhere & "([AssignedTo] Like ""*" & Me.AssignedTo & "*"")"

At first glance, it looks like this should be

strWhere = strWhere & "([AssignedTo] Like [COLOR=red]'*"[/COLOR] & Me.AssignedTo & [COLOR=red]"*')"
[/COLOR]

You are also not setting strWhere to a zero length string at the beginning

You are not using AND between each condition

 

[rwrig]

CJ London thanks for your reply not sure what you mean setting strWhere to a zero length string at the beginning

I implemented the recommended changes:


Private Sub Search_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.AssignedTo) Then
strWhere = strWhere & "([AssignedTo] Like '*" & Me.AssignedTo & "*') AND"
End If
If Not IsNull(Me.OpenedBy) Then
strWhere = strWhere & "([OpenedBy] Like '*" & Me.OpenedBy & "*') AND"
End If
If Not IsNull(Me.Status) Then
strWhere = strWhere & "([Status] Like '*" & Me.Status & "*')AND"
End If
If Not IsNull(Me.Category) Then
strWhere = strWhere & "([Category] Like '*" & Me.Category & "*')AND"
End If
If Not IsNull(Me.Priority) Then
strWhere = strWhere & "([Priority] Like '*" & Me.Priority & "*')AND"
End If
If Not IsNull(Me.OpenedDateFrom) Then
strWhere = strWhere & "([EnteredOn] >= " & Format(Me.OpenedDateFrom, conJetDate) & ") AND "
End If
If Not IsNull(Me.DueDateFrom) Then
strWhere = strWhere & "([EnteredOn] <= " & Format(Me.DueDateFrom, conJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
End If
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub