Filter Report From Selected Option Box (1 Viewer)

Enginerd

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

I am creating a report that will show company contact information and I want to be able to filter it by the person's age. The person's age does not show up on the report but I want to have the user select from three option boxes whether they want to view all "Junior Members" (anyone under the age of 18 at the running of the report), all "Senior Members" (anyone over the age of 18 at the running of the report), or all members regardless of age. I have looked and researched all over the internet for an solution to my problem and I just can't seem to make sense of anything or find what I am looking for. In short, what I am trying to do is this: the user clicks on a reports button on a form and it brings up another form that has option buttons for all the forms they can run. Right now I am just working on the report for member contact information. So what would happen when the user gets to this reports screen is they go and select the junior member, senior member, or all member option button and hit run. The program will then, using VBA code, filter the data based on the given parameters that I explained above and spit out the report with the data as filtered.

The only other piece that I want to include if possible pertains to the reports that contain all member contact information. I want the report to run and display all contact information for all members with the added piece that I want it to sort the member information data, if possible, by whether or not the member is a junior or senior member. I hope that all of this makes sense but please feel free to ask for more clarification on my question.
 

plog

Banishment Pending
Local time
Today, 15:27
Joined
May 11, 2011
Messages
11,611
First you need to make the underlying data of that report have that data. Hopefully that report is based on a query, if not, time to do so. In that query you will include a calculated field that determines if the user is Senior or Junior, let's call that new field [MemberType] with those 2 string values ('Junior', 'Senior') as its options.

Then on your form you add your drop down which has 3 options (Junior, senior, all). Then a button that triggers VBA code. In that VBA code you will ultimate use DoCmd.OpenReport (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx) to open your report. However, before you do, you will construct a filter string to limit the report to just the records you want. Build that string (or don't use it at all in the case of All) and call DoCmd.OpenReport to give the user their requested report.
 

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
thanks plog, I appreciate your response about making the query that includes the junior or senior member type. However, I need the report to be run with option buttons and not a drop down combo box. This will be much more efficient and user friendly for the people who would be running the report. I am trying to make this as user friendly as possible. Also, I don't understand what you mean by applying the filter for junior, senior or all depending on the option box that is selected. This is really where my problem lies and where I need more explanation as to what I am doing and why.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
The principle is identical with option buttons.

BTW - there was an almost identical question related to over/under 18 year olds recently.
Was that also you?
If not, try searching for it...
 
Last edited:

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
The principle is identical with option buttons.

Hi ridders, I am confused though as to how to alter the VBA code such that when a particular option box is selected, the filter is applied to only display the data that meets that filter's requirements. Does this make sense?
 

plog

Banishment Pending
Local time
Today, 15:27
Joined
May 11, 2011
Messages
11,611
You can't alter something that doesn't exist. Do you in fact have any code? If so, post it here so we can help.

I myself, gave no code, just the specific tasks you needed to do to achieve your ultimate aim. You might get lucky and have someone come along and code this whole thing for you to your exact specifications, but I doubt it. It's just enough work to be too much work for not being paid, and not challenging enough for someone to solve just for the fun of it. I really think you are going to have to do this yourself.

So, get to it, and when you get stuck in a specific spot, post back here and we will help.
 

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
You can't alter something that doesn't exist. Do you in fact have any code? If so, post it here so we can help.

I myself, gave no code, just the specific tasks you needed to do to achieve your ultimate aim. You might get lucky and have someone come along and code this whole thing for you to your exact specifications, but I doubt it. It's just enough work to be too much work for not being paid, and not challenging enough for someone to solve just for the fun of it. I really think you are going to have to do this yourself.

So, get to it, and when you get stuck in a specific spot, post back here and we will help.

plog, you did actually post code in your response when you mentioned the DoCmd.OpenReport and that I need to apply filters by coding them in in VBA. I understand the DoCmd.OpenReport part. That is not where I am stuck. I do not understand what to do in order to apply the filters based on what is selected. And I am not asking anyone here to write code for me and completely solve this problem for me. What I am simply asking for is if someone could post generic syntax about how to filter the data, then I can work on trying fill in the blanks of how the filter works and then I tell access to run the report based on the given criteria.
 

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
Here's a bare outline for you to develop where fraReportType is the option group with 3 buttons:

Code:
Private Sub fraReportType_AfterUpdate()

Dim strCriteria As String
Set strCriteria as filter criteria 

Select Case Me.fraReportType
    Case 1 'under 18
        strCriteria = ...
        DoCmd.OpenReport "rptReportName, acViewPreview, , strCriteria
    Case 2 'over 18
        strCriteria
        DoCmd.OpenReport "rptReportName, acViewPreview, , strCriteria
    Case 3 'all - no filter
       DoCmd.OpenReport "rptReportName, acViewPreview		
    End Select

End Sub

Define your own filter criteria for cases 1 & 2
You should add error handling as well
 

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
Thank you Collin for giving me a starting point as to where to go from here. The following is what I have come up with. I'm sure it's far from correct but it's a start.

Code:
Private Sub cmdMembers_Click()
'runs report to show member information based on option selected
Dim strCriteria As String

Select Case Me.fraMemberContact
    Case 1 'under 18
        strCriteria = "Junior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, [MemberType] = strCriteria
    Case 2 'over 18
        strCriteria = "Senior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, [MemberType] = strCriteria
    Case 3 'all - no filter
       DoCmd.OpenReport "rptMemberContact"
    End Select

End Sub

My query qryMemberType contains each member's name, DOB and a column called MemberType that contains an If Then statement that compares the DOB to the current date to determine the person's age and assigns the value either "Junior" or Senior" if the value is greater than or less than 18. strCriteria is supposed to be used to specify when MemberType equals this do this and what I am getting is the following: "Microsoft Access can't find the field '|1' referred to in your expression"
 

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
You're making this too complicated

The report syntax is:
DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

DON'T include the query name
DO give the report view e.g. acViewPreview
With a filter this simple, the strCriteria line is unnecessary
However you do need text delimiters

So for case 1,

Case 1 'under 18
DoCmd.OpenReport "rptMemberContact", acViewPreview, , "MemberType = 'Junior'"

etc
 

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
You're making this too complicated

The report syntax is:
DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

DON'T include the query name
DO give the report view e.g. acViewPreview
With a filter this simple, the strCriteria line is unnecessary
However you do need text delimiters

So for case 1,

Case 1 'under 18
DoCmd.OpenReport "rptMemberContact", acViewPreview, , "MemberType = 'Junior'"

etc

I'm confused as to why I don't need to call the query in order to tell the report how to filter the data. Also, that example you sent me doesn't work either. I changed everything to the following:
Code:
Private Sub cmdMembers_Click()
'runs report to show member information based on option selected
Dim strCriteria As String

Select Case Me.fraMemberContact
    Case 1 'under 18
        strCriteria = "Junior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, "MemberType = strCriteria"
    Case 2 'over 18
        strCriteria = "Senior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, "MemberType = strCriteria"
    Case 3 'all - no filter
       DoCmd.OpenReport "rptMemberContact"
    End Select

End Sub
Now when I hit select one of the three option buttons and hit run, I get an Enter Parameter Value box for the member type.



***I might have answered my own question because I don't think I ended up creating my report based off of the query and did it based off of a table instead. I will update you when I figure this out. My question is though if I am creating a report based off of a query, does the piece that I am filtering the report off of have to be in the report or just in the query?
 
Last edited:

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
I'm confused as to why I don't need to call the query in order to tell the report how to filter the data. Also, that example you sent me doesn't work either. I changed everything to the following:
Code:
Private Sub cmdMembers_Click()
'runs report to show member information based on option selected
Dim strCriteria As String

Select Case Me.fraMemberContact
    Case 1 'under 18
        strCriteria = "Junior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, "MemberType = strCriteria"
    Case 2 'over 18
        strCriteria = "Senior"
        DoCmd.OpenReport "rptMemberContact", , qryMemberType, "MemberType = strCriteria"
    Case 3 'all - no filter
       DoCmd.OpenReport "rptMemberContact"
    End Select

End Sub
Now when I hit select one of the three option buttons and hit run, I get an Enter Parameter Value box for the member type.



***I might have answered my own question because I don't think I ended up creating my report based off of the query and did it based off of a table instead. I will update you when I figure this out. My question is though if I am creating a report based off of a query, does the piece that I am filtering the report off of have to be in the report or just in the query?

Ok, so I ended up redoing the report, making it off the query. I do not include the member type column in the report because this is redundant information and I do not want it to show up in the report. My code is now as follows:
Code:
Private Sub cmdMembers_Click()
'runs report to show member information based on option selected
Dim strCriteria As String

Select Case Me.fraMemberContact
    Case 1 'under 18
        strCriteria = "Junior"
        DoCmd.OpenReport "rptMemberContact", , , [MemberType] = strCriteria
    Case 2 'over 18
        strCriteria = "Senior"
        DoCmd.OpenReport "rptMemberContact", , , [MemberType] = strCriteria
    Case 3 'all - no filter
       DoCmd.OpenReport "rptMemberContact"
    End Select

End Sub

I am back to getting the "Microsoft Access can't find the field '|1' referred to in your expression" error now.
 

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
I'm sorry but you've completely ignored what I said in my last post.
So I'm going to leave it to someone else ....
Try doing what I said as a starting point!
 

Enginerd

Registered User.
Local time
Today, 16:27
Joined
Jun 5, 2017
Messages
16
I'm sorry but you've completely ignored what I said in my last post.
So I'm going to leave it to someone else ....
Try doing what I said as a starting point!

I'm sorry but I have not completely ignored what you said. I just noticed what you said about not needing the strCriteria part. I have updated my code to the following and am now getting an Enter Parameter Value error when I hit run.
Code:
Private Sub cmdMembers_Click()
'runs report to show member information based on option selected
Select Case Me.fraMemberContact
    Case 1 'under 18
        DoCmd.OpenReport "rptMemberContact", , , "MemberType = Junior"
    Case 2 'over 18
        DoCmd.OpenReport "rptMemberContact", , , "MemberType = Senior"
    Case 3 'all - no filter
       DoCmd.OpenReport "rptMemberContact"
    End Select

End Sub

The only thing I can think about is that it's not liking the fact that I am using MemberType in the query and I am trying to filter the data off of this value but I do not include it in the report. The reason I don't want it to show in the report is because it's redundant information to filter it by junior and senior and yet have a column completely filled with either junior or senior show up on the report.
 

isladogs

MVP / VIP
Local time
Today, 20:27
Joined
Jan 14, 2017
Messages
18,186
My FINAL reply...

Copy EXACTLY what I wrote in post 10
 

Users who are viewing this thread

Top Bottom