Apply multiple filters to report in VBA code (1 Viewer)

Enginerd

Registered User.
Local time
Today, 06:03
Joined
Jun 5, 2017
Messages
16
Hi,

I created a report based on a query for my database. The report creates a sign-in sheet for like a class, training, etc.. The data shown on the report is filtered by VBA code based on the selected option box on the report run form. The issue that I am running into is that for one of the option boxes I have several pieces of data that I need to be able to filter the report for. Another way of putting this is the following: In the query that the report is based off of, I have a field that says if a member is an FF, EMT, FF/EMT, Social, Prob/FF, Prob/EMT, Prob/FFEMT, or Prob/Social. What the option boxes will do is tell the report which data to display based on filtering the data by the member's certification specifically. The code that I have works for FF, EMT, FF/EMT, Social and all members (no filters applied) however when the Probationary option box is checked, I need to have the report filter to show all Prob/FF, Prob/EMT, Prob/FFEMT and Prob/Social members and not just one or the other. The code as I currently have it written is the following:
Code:
Private Sub cmdPersonnel_Click()
'runs report to show only FF, EMT, FF/EMT, Social, Probationary or all Members
Select Case Me.fraPersonnelType
    Case 1 'FF
        DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'FF'"
    Case 2 'EMT
        DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'EMT'"
    Case 3 'FF/EMT
       DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'FF/EMT'"
    Case 4 'Social
        DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'Social'"
    Case 5 'Probationary Members
        DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'Prob/FF'" Or "MemberCert = 'Prob/EMT'" Or "MemberCert = 'Prob/FFEMT'" Or "MemberCert = 'Prob/Social'"
    Case 6 'All members
        DoCmd.OpenReport "rptPersonnelType", acViewReport, , , , "MemberCert"
    End Select
    
End Sub
When I try to run the report to get a list of all probationary members, I get the following error "Run-time error '13': Type mismatch". Any insight that someone can give me to help me move in the right direction as to how I can potentially filter the data is appreciated. One thing I considered is that I might need to create another query that says if the member cert column is filled with Prob/FF, Prob/EMT, Prob/FFEMT or Prob/Social then display Probationary and then filter off of this but I'd prefer not to if I can just modiy the code slightly.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:03
Joined
Aug 30, 2003
Messages
36,118
Delete the double quotes surrounding each OR.
 

Enginerd

Registered User.
Local time
Today, 06:03
Joined
Jun 5, 2017
Messages
16
Delete the double quotes surrounding each OR.

Hi pbaldy, thanks for your response. I did try this already and I received a similar if not the same response as explained above. Unless I'm misunderstanding what you're telling me to do.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:03
Joined
Aug 30, 2003
Messages
36,118
Hard to help without seeing what you tried. ;)

DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'Prob/FF'" Or "MemberCert = 'Prob/EMT'" Or "MemberCert = 'Prob/FFEMT'" Or "MemberCert = 'Prob/Social'"
 

Enginerd

Registered User.
Local time
Today, 06:03
Joined
Jun 5, 2017
Messages
16
Hard to help without seeing what you tried. ;)

DoCmd.OpenReport "rptPersonnelType", acViewReport, , "MemberCert = 'Prob/FF'" Or "MemberCert = 'Prob/EMT'" Or "MemberCert = 'Prob/FFEMT'" Or "MemberCert = 'Prob/Social'"

pbaldy, What I am saying is that I tried removing the double quotes that you show in red. When I did this, I receive the same message as when they were in the code.

****Correction this now works and I am all set. Thank you for your help. I don't know what mistake I was making before.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:03
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted.
 

Users who are viewing this thread

Top Bottom