Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-08-2017, 07:25 AM   #1
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Filter Report From Selected Option Box

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.

Enginerd is offline   Reply With Quote
Old 06-08-2017, 07:39 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,326
Thanks: 10
Thanked 2,263 Times in 2,215 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Filter Report From Selected Option Box

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/lib.../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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Enginerd (06-08-2017)
Old 06-08-2017, 08:16 AM   #3
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

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 by Enginerd; 06-08-2017 at 08:32 AM.
Enginerd is offline   Reply With Quote
Old 06-08-2017, 08:29 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter Report From Selected Option Box

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...
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 06-08-2017 at 08:35 AM.
isladogs is offline   Reply With Quote
Old 06-08-2017, 08:34 AM   #5
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

Quote:
Originally Posted by ridders View Post
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?
Enginerd is offline   Reply With Quote
Old 06-08-2017, 08:48 AM   #6
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,326
Thanks: 10
Thanked 2,263 Times in 2,215 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Filter Report From Selected Option Box

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 is offline   Reply With Quote
Old 06-08-2017, 08:55 AM   #7
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

Quote:
Originally Posted by plog View Post
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.

Enginerd is offline   Reply With Quote
Old 06-08-2017, 09:23 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter Report From Selected Option Box

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Enginerd (06-08-2017)
Old 06-08-2017, 09:57 AM   #9
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

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"
Enginerd is offline   Reply With Quote
Old 06-08-2017, 10:16 AM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter Report From Selected Option Box

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Enginerd (06-08-2017)
Old 06-08-2017, 10:34 AM   #11
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

Quote:
Originally Posted by ridders View Post
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 by Enginerd; 06-08-2017 at 10:42 AM.
Enginerd is offline   Reply With Quote
Old 06-08-2017, 10:50 AM   #12
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

Quote:
Originally Posted by Enginerd View Post
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.
Enginerd is offline   Reply With Quote
Old 06-08-2017, 11:12 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter Report From Selected Option Box

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!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-08-2017, 11:37 AM   #14
Enginerd
Newly Registered User
 
Join Date: Jun 2017
Posts: 16
Thanks: 5
Thanked 0 Times in 0 Posts
Enginerd is on a distinguished road
Re: Filter Report From Selected Option Box

Quote:
Originally Posted by ridders View Post
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.
Enginerd is offline   Reply With Quote
Old 06-08-2017, 11:40 AM   #15
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,080
Thanks: 110
Thanked 2,736 Times in 2,498 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Filter Report From Selected Option Box

My FINAL reply...

Copy EXACTLY what I wrote in post 10

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
filters , option buttons , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Option Groups to filter report gcarpenter Reports 5 11-18-2013 01:22 PM
Using an option group to filter a report ErinL Forms 3 09-25-2012 09:43 AM
Filter report selected from Listbox Nizar General 0 01-05-2008 01:05 PM
Report based on selected pop up option selected NEEDHELP123 Reports 2 06-08-2006 09:53 AM
Filter By Selection/Form - then report selected only vangogh228 Forms 3 04-20-2005 03:46 AM




All times are GMT -8. The time now is 12:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World