FILTERING REPORTS (1 Viewer)

mercystone

Member
Local time
Today, 09:27
Joined
Sep 20, 2021
Messages
108
How are you doing everyone? I have a question? How do you filter a report from a tables which the user entered the fields manually? For example:
Name. City. Age
John brown. New York. 35
John brown. Oxford. 40
In this case john brown is from usa and another john brown from England. The user entered those names manually from different subforms.
 

June7

AWF VIP
Local time
Yesterday, 22:27
Joined
Mar 9, 2014
Messages
4,381
What do you want to filter for - one John Brown - which one? Apply criteria to however many fields needed to assure desired record is returned.
 

jdraw

Super Moderator
Staff member
Local time
Today, 02:27
Joined
Jan 23, 2006
Messages
14,318
Please tell us more about the application, the issue and criticality. If different countries is an issue, why not include Country in your design/data entry? How do subforms fit into your design?
May be just me, but I don't understand what your expectations are/were.
 

GinaWhipp

AWF VIP
Local time
Today, 02:27
Joined
Jun 21, 2011
Messages
5,881
Hmm, what is the Primary Key? Because that is what you should be filtering on. If you made the field that holds the Name the Primary Key then that's the problem. There needs to a separate one and you can use Autonumber as the Data Type.
 

mercystone

Member
Local time
Today, 09:27
Joined
Sep 20, 2021
Messages
108
In a form the filter works perfectly. I am defeated on how to use it on a report.:-

Private Sub Command221_click()
Form.Country.SetFocus
DoCmd.RunCommand acCmdFilterMenu
End Sub


I now need it to apply in a report so that the user can print only certain names e.g People from New York, USA only.
 

June7

AWF VIP
Local time
Yesterday, 22:27
Joined
Mar 9, 2014
Messages
4,381
Apply filter in the OpenReport command.
 

mercystone

Member
Local time
Today, 09:27
Joined
Sep 20, 2021
Messages
108
How?
Private Sub Report_Open(Cancel As Integer)
?
?
End Sub

Please help
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:27
Joined
Sep 21, 2011
Messages
10,555
Do it on the open report command ?
That is how I normally filter a report?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:27
Joined
Jul 9, 2003
Messages
14,519
There are several ways of applying a filter to a report. In the following video I demonstrate a method I use...

Filter a Report from a Form - Nifty Access​


On my website here, I demonstrate a way to create filters easily:-


In the above video I demonstrate some code for checking a combo box is not empty. I realised that this single line of code evolved from multiple lines of code. I thought "That's a good example for someone new to creating functions". In particular this function shows how you can pass an object into a function, process it, and return a Boolean Value, telling your code the process was successful (or not). The following YouTube Video demonstrates the evolution of the code, some of the hiccups and problems, and finally the solution!


Writing Your First Object Function - Nifty Access​


UPDATE!!!
I wasn't happy with the previous video because I didn't cover how it checks the combo boxes very well. I still don't think I've quite got it right, anybody want to chime in with any tips how I can improve my presentation I'd be most grateful!

Writing Your First Object Function - Extra - Nifty Access​

 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:27
Joined
Feb 19, 2002
Messages
36,327
When you open the report using the OpenReport method, you have the option of specifying a filter or a where clause. Use one of those.

If your user is opening the report from the Navigation pane, you need to add a menu or switchboard to the app. The user should NEVER be exposed to objects other than forms or reports.
 

mercystone

Member
Local time
Today, 09:27
Joined
Sep 20, 2021
Messages
108
Kindly, I am requesting anyone to assist me with a sample database with OpenReport method. Thanks. Merry Christmas and Happy New year everybody.
 

June7

AWF VIP
Local time
Yesterday, 22:27
Joined
Mar 9, 2014
Messages
4,381
The OpenReport method is quite simple. There are enough examples and discussion out there on this topic to provide adequate explanation of its use. Gizmo provided you two tutorials. Also explore this tutorial http://allenbrowne.com/ser-62.html
If still need help, post your db for analysis.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:27
Joined
Feb 19, 2002
Messages
36,327
If you add a button to a form and you have wizards enabled, it will walk you through the process. Problem is that it will create an embedded macro. After you check that the button works, put the form back in design view and check the convert macros option in the ribbon to make it VBA instead. There is no time like the present to learn how to do the little things for yourself. The macro feature creates crappy code but it is a start.
 

SHANEMAC51

Active member
Local time
Today, 09:27
Joined
Jan 28, 2022
Messages
288
How are you doing everyone? I have a question? How do you filter a report from a tables which the user entered the fields manually? For example:
Name. City. Age
John brown. New York. 35
John brown. Oxford. 40
In this case john brown is from usa and another john brown from England. The user entered those names manually from different subforms.
you can filter by any combination of fields
- substitution - only by the full value, sometimes it's inconvenient when you don't know the name / surname exactly
- text field - you can search by part of the value, for example *carolina*
- numeric field - can be searched by interval, two search fields
- date field - you can search by interval, two search fields, or only year or year + quarter, the largest variety of options
 

Users who are viewing this thread

Top Bottom