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:
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.
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