Filter Report Using 3 ListBoxes (MultiSelect) (1 Viewer)

[jadehawk]

Hi everyone,

Since I started using Access I have made it so far without asking too many questions but now I am stuck and need assistance..

Included in the zip. is a working version of my problem.

My table "EmpTraining" will store 5 fields:

• ID (PK Autonumber )
• EmpID (From Employees Table)
• FleetName_ID ( From FleetList Table )
• Engine1_ID (From EngineList Table )
• Engine2_ID (From EngineList Table )

At the point of adding a record to EmpTraining table , EmpID and FleetName_ID and Engine1_ID are Required, Engine2_ID is optional.


I am having trouble generating a Report Filter that will use 3 Listboxes(multiselect) and be able to limit the report by what is selected.

My Listboxes filter:

• EmpBase (from Employees Table)
• FleetName_ID ( From FleetList Table )
• Engine1_ID (From EngineList Table )

I am trying to use the Third listbox to filter both Engine1_ID and Engine2_ID and I think this is my main problem.

On the Filter form that has the ListBoxes I use a button to apply the filter to the report, the code for this Onclick event is:

Private Sub btnApplyFilter_Click()
 Dim varItem As Variant
    Dim strBaseStation As String
    Dim strFleetType As String
    Dim strEngineType As String
    Dim strFilter As String
 
'Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "TechnicalTrainingReport") <> acObjStateOpen Then
        DoCmd.OpenReport "TechnicalTrainingReport", acViewPreview
        Exit Sub
    End If

' Build criteria string from lstBaseStation listbox
    For Each varItem In Me.lstBaseStation.ItemsSelected
        strBaseStation = strBaseStation & "," & Me.lstBaseStation.ItemData(varItem)
    Next varItem
    If Len(strBaseStation) = 0 Then
        strBaseStation = "Like '*'"
    Else
        strBaseStation = Right(strBaseStation, Len(strBaseStation) - 1)
        strBaseStation = "IN (" & strBaseStation & ")"
    End If

' Build criteria string from lstFleetType listbox
    For Each varItem In Me.lstFleetType.ItemsSelected
        strFleetType = strFleetType & "," & Me.lstFleetType.ItemData(varItem)
    Next varItem
    If Len(strFleetType) = 0 Then
        strFleetType = "Like '*'"
    Else
        strFleetType = Right(strFleetType, Len(strFleetType) - 1)
        strFleetType = "IN (" & strFleetType & ")"
    End If

' Build criteria string from lstEngineType listbox
    For Each varItem In Me.lstEngineType.ItemsSelected
        strEngineType = strEngineType & "," & Me.lstEngineType.ItemData(varItem)
    Next varItem
    If Len(strEngineType) = 0 Then
        strEngineType = "Like '*'"
    Else
        strEngineType = Right(strEngineType, Len(strEngineType) - 1)
        strEngineType = "IN (" & strEngineType & ")"
    End If

' Build filter string
    strFilter = "[EmpBase] " & strBaseStation & _
                " AND [FleetName_ID] " & strFleetType & _
                " AND [Engine1_ID] " & strEngineType & _
                " AND [Engine2_ID] " & strEngineType
                
MsgBox "" & strFilter & "."
' Apply the filter and switch it on
    With Reports("TechnicalTrainingReport")
        .Filter = strFilter
        .FilterOn = True
    End With

End Sub

I have tried to change the Build Filter String Section to this with no help:

' Build filter string
    strFilter = "[EmpBase] " & strBaseStation & _
                " AND [FleetName_ID] " & strFleetType & _
                " AND [Engine1_ID] " & strEngineType & _
                " OR [Engine2_ID] " & strEngineType

The only time I get the filter to almost work is when the Build Filter String Section is like this (As you can tell I am only filtering Engine1_ID:

' Build filter string
    strFilter = "[EmpBase] " & strBaseStation & _
                " AND [FleetName_ID] " & strFleetType & _
                " AND [Engine1_ID] " & strEngineType

'                " AND [Engine2_ID] " & strEngineType

How can I also make it filter the Engine1_ID and Engine2_ID fields .?

Or Should I store the FleetName/Engine1/Engine2 in a different way?

I hope you understand my problem.
Thank you for any help or advice.

 

[MarkK]

I have only glanced quickly at your post, and I don't see any comparison operators in your filter strings, like this . . .

    strFilter = "[EmpBase] " & strBaseStation & _
                " AND [FleetName_ID] " & strFleetType & _
                " AND [Engine1_ID] " & strEngineType & _
                " OR [Engine2_ID] " & strEngineType

. . . should be . . .

    strFilter = "[EmpBase] = " & strBaseStation & _
                " AND [FleetName_ID] = " & strFleetType & _
                " AND [Engine1_ID] = " & strEngineType & _
                " OR [Engine2_ID] = " & strEngineType

See what I mean?

 

[jadehawk]

Hi MarkK Thank you for your reply.. I added the comparison Operators ( = ) to the filter string and It returns and error (I get error even If a Select one item from each listbox).

Attached 4 images one is a msgbox showing the String that will be used as a filter, and the second is the error that Access returns.. If I delete the ( = ) and do not try to filter Engine2_ID the String works and I get no error from Access.
This is what I used and got the error (force-closes report after hitting OK)

2 of the images are the same as above but with a single item selected on each listbox

' Build filter string
    strFilter = "[EmpBase] = " & strBaseStation & _
                " AND [FleetName_ID] = " & strFleetType & _
                " AND [Engine1_ID] = " & strEngineType & _
                " OR [Engine2_ID] = " & strEngineType

If I use this one, report shows and filters but I am not filtering Engine2_ID

' Build filter string
    strFilter = "[EmpBase] " & strBaseStation & _
                " AND [FleetName_ID] " & strFleetType & _
                " AND [Engine1_ID] " & strEngineType

 

[jadehawk]

Well Friends I posted the same question on another forum and I was lucky to also get help there as well.

I finally got what I needed to work thanks to the help from another forum member. I was made to see that I had a problem with how I was buildind my Filter String and a Simple Parenthesis fixed the problem..

I'll repost the code I was given and my reply to it from the other forum for anyone else here that may find it useful..

REPLY from Forum Guru! (asolder) :

The more direct you are in building the criteria, the better. You can also omit the Like * by eliminating fields with no selections.

' Build criteria string from lstBaseStation listbox
    For Each varItem In Me.lstBaseStation.ItemsSelected
        strBaseStation = strBaseStation & "," & Me.lstBaseStation.ItemData(varItem)
    Next varItem
    If Len(strBaseStation) > 0 Then
        strBaseStation = " XXX [EmpBase] IN (" & Mid(strBaseStation,2) & ") " 'the XXX will be trimmed in the final step
    End If

' Build criteria string from lstFleetType listbox
    For Each varItem In Me.lstFleetType.ItemsSelected
        strFleetType = strFleetType & "," & Me.lstFleetType.ItemData(varItem)
    Next varItem
    If Len(strFleetType) > 0 Then
        strFleetType = " AND [FleetName_ID]  IN(" & Mid(strFleetType,2) & ") "
    End If

' Build criteria string from lstEngineType listbox
    For Each varItem In Me.lstEngineType.ItemsSelected
        strEngineType = strEngineType & "," & Me.lstEngineType.ItemData(varItem)
    Next varItem
    If Len(strEngineType) > 0 Then
        strEngineType = " AND ([Engine1_ID]  IN (" & Mid(strEngineType,2) & ")  " & _
                                  " OR [Engine2_ID]  IN (" & Mid(strEngineType,2) & ") ) "
    End If

' Build filter string (Note preceding XXX and AND trimmed)
    strFilter = Mid(strBaseStation & & strFleetType &  strEngineType, 5)
                
MsgBox "" & strFilter & "."
' Apply the filter and switch it on
    With Reports("TechnicalTrainingReport")
If len(strfilter)> 0 then
        .Filter = strFilter
        .FilterOn = True
Else    'make sure filter is off if there are no criteria
         .FilterOn = False
end if
    End With

End Sub

Note I used the OR between engine2_id and engine1_id. I also added precedence to the OR operation by putting it in parentheses. In the event Engine2_ID was blank for some reason, the whole expression would evaluate to false.

At some point in the future you could add another engine to the class. Rather than redesign your database at that time you should have yet another table:
EmpTrainingEngines
EmpTrainingID
EngineID


Dropping the Engine references in EmpTraining.

I'm assuming, of course, that EmpBase is numeric.


#############################################
#############################################
My Reply:

Thank you Azolder!!!

Almost 2 weeks stuck in this problem and all it took was a simple Parenthesis surrounding the OR Operator!!.. I should have asked for help sooner!

Here is what I have learned thanks to your reply that relates to my personal setup on this DB.

• When using a STRING to filter an Already opened report I do not need to enter [Like "*"] for any ListBox that is left blank.
• The use of the Mid() Function to Strip away Preceding Character that are not needed on final Filter String Construction.
• To Take advantage of how Mid() works and use fill characters [XXX] to meet the format that will be needed to Build the Final filter string (specific to this particular example and DB).
• When filtering Engine1_ID and Engine2_ID The proper way was always the OR operator, but I was missing the KEY ingredient that was to enclose those two Engine1_ID OR Engine2_ID in Parenthesis!! (Engine1_ID OR Engine2_ID) to give precedence to the OR operator calculation.

I have modified the code on my real project DB to account for the lack of '()' and for now simple replaced the Like "*" for just "". I then tested my filter form and all works as I needed it to. I will rework the code to generate each listbox result selection the way you showed me and will continue to use that format. I can finally take a break on this and continue on my next section on this project DB.

You have no Idea how much you have helped.
I got sucked it to a small project that has become a Giant!! I started with nothing but Google and forums like this..

Thank you again.
Paul...


##############################################
##############################################
I hope this helps someone else or at the very least serve as an idea on how to approach a problem like this.

Thank you MarkK for your help too.. All is appreciated