Form/Query Filter Design - Postal...

[Guest]

I have zero knowledge of visual basic and am having a very difficult time
trying to get my project to work. I need help in layman's terms...

The following code works great. It allows me to select multiple items from
a listbox and run a query that is filtered by the selected items. Now for
the part I have been struggling with. I want to be able to add multiple list
boxes to the form so that I can filter on item 1 AND item2 AND item3.

For example, this code lets me select employee names. The second List Box
(called lstLOB) contains the Line of Business. Since employees handle
multiple lines, I want to be able to run the query on Employee Name AND Line
of Business.

For someone that doesnt understand coding, how would you add/modify this?
Please...someone throw me a bone...

Peter

Private Sub cmdFilter_Click()

Dim varItem As Variant
Dim strWhere As String
Dim strSQL As String

'build criteria for the selected employees
For Each varItem In Me.lstEmp.ItemsSelected
strWhere = strWhere & Chr(34) & Me.lstEmp.ItemData(varItem) & Chr(34) &
", "
Next varItem

'make sure at least 1 employee was selected
If Len(strWhere) > 0 Then
strSQL = "[Employee Name] IN (" & Left$(strWhere, Len(strWhere) - 2) & ")"
End If


'this changes the actual SQL of your query based on the items selected in
the listboxes
'and is the very last line of code to execute
CurrentDb.QueryDefs("zzzHelpMeQuery").SQL = "SELECT * FROM Joined WHERE " &
strSQL

End Sub

 

[Guest]

In SQL, you add additional information to your "where" clause by simply
adding the word "AND" at the end followed by more SQL that specifies
information for another field to be filtered upon.

After you have created the employee portion of your SQL statement, build
another strWhere that refers to the Line of Business instead of the Employee.
Just copy the code that creates strWhere, but change it to refer to the line
of business instead of the employee.

Don't copy & re-run the Dim statements, though, or it will reset the values
and you will get ONLY the line of business filter.

In the last step of the copied code, where you assign the value to strSQL,
ADD it to the end of the already-created strSQL (that contains the Employee
portion) instead of creating strSQL from scratch again, like this: strSQL =
strSQL & "AND strWhere...".

If the user did not pick an employee, but did pick a line of business, then
we simply create strSQL without the AND in front, since there is no
pre-existing (i.e. employee-related) strSQL to which to add it.

I have not tested the code below, but I just copied the germane portions and
inserted them in the correct place. I referrred to the line of business
filter as "lstLineOfBusiness". You will need to replace that with the correct
reference to the box that is used to filter by line of business.

Private Sub cmdFilter_Click()

Dim varItem As Variant
Dim strWhere As String
Dim strSQL As String

'build criteria for the selected employees
For Each varItem In Me.lstEmp.ItemsSelected
strWhere = strWhere & Chr(34) & Me.lstEmp.ItemData(varItem) & Chr(34) &
", "
Next varItem

'make sure at least 1 employee was selected
If Len(strWhere) > 0 Then
strSQL = "[Employee Name] IN (" & Left$(strWhere, Len(strWhere) - 2) & ")"
End If

'build criteria for the selected line of business
For Each varItem In Me.lstLineOfBusiness.ItemsSelected
strWhere = strWhere & Chr(34) & Me.lstLineOfBusiness.ItemData(varItem) &
Chr(34) &
", "
Next varItem

'make sure at least 1 line of business was selected
If Len(strWhere) > 0 Then
If Len(strSQL) > 0 then
strSQL = strSQL & "and [Line Of Business] IN (" & Left$(strWhere,
Len(strWhere) - 2) & ")"
Else
strSQL = "[Line Of Business] IN (" & Left$(strWhere, Len(strWhere) - 2)
& ")"
End If

I have zero knowledge of visual basic and am having a very difficult time
trying to get my project to work. I need help in layman's terms...

The following code works great. It allows me to select multiple items from
a listbox and run a query that is filtered by the selected items. Now for
the part I have been struggling with. I want to be able to add multiple list
boxes to the form so that I can filter on item 1 AND item2 AND item3.

For example, this code lets me select employee names. The second List Box
(called lstLOB) contains the Line of Business. Since employees handle
multiple lines, I want to be able to run the query on Employee Name AND Line
of Business.

For someone that doesnt understand coding, how would you add/modify this?
Please...someone throw me a bone...

Peter

Private Sub cmdFilter_Click()

Dim varItem As Variant
Dim strWhere As String
Dim strSQL As String

'build criteria for the selected employees
For Each varItem In Me.lstEmp.ItemsSelected
strWhere = strWhere & Chr(34) & Me.lstEmp.ItemData(varItem) & Chr(34) &
", "
Next varItem

'make sure at least 1 employee was selected
If Len(strWhere) > 0 Then
strSQL = "[Employee Name] IN (" & Left$(strWhere, Len(strWhere) - 2) & ")"
End If


'this changes the actual SQL of your query based on the items selected in
the listboxes
'and is the very last line of code to execute
CurrentDb.QueryDefs("zzzHelpMeQuery").SQL = "SELECT * FROM Joined WHERE " &
strSQL

End Sub