Multiple Criteria search form help with the VBA Code

[rwrig]

Hey Guys I need help constructing the VBA code for this form so that a user can be able to select the multiple criteria and click search and the table below will filter the criteria selected. I included the screen shot and all fields names are the same names as the labels. Here's my current code:

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)
Me.Filter = strWhere
Me.FilterOn = True

End Sub

Any help will be greatly appreciated

 

[burrina]

So, what is the problem? When you debug, does it error out and if so, on what line? What exactly happens when you try and run it? Details Please!

 

[rwrig]

For some reason Nothing happens, I will choose a name from the drop down box and nothing happens when I click Search. All I did was paste the code into the event procedure vba.

 

[burrina]

Hard to say without seeing what you are seeing. Debug one line at a time and run.
For example, what happens if you ONLY enter one criteria?

 

[rwrig]

As of right now I'm not seeing a debug issues because when I click the button the even procedure isn't running do you know why this issue can be happening?

 

[burrina]

You Debug by going in design mode and running the code step by step. Have you tried that?

 

[rwrig]

When I highlight the code and click run it brings up the macros box. See attachment.

 

[burrina]

No, rem out the code and only use one line at a time and run and see what happens.

Try this ONLY.
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
Disable the other If statements by putting a ' in front of the line of code.

 

[rwrig]

First Error I received was Compile error: invalid use of Me keyword.