FILTERING REPORTS

[mercystone]

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]

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.

 

[Uncle Gizmo]

The user entered those names manually from different subforms.

Why?

 

[jdraw]

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]

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]

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]

Apply filter in the OpenReport command.

 

[mercystone]

How?
Private Sub Report_Open(Cancel As Integer)
?
?
End Sub

Please help

 

[Gasman]

Do it on the open report command ?
That is how I normally filter a report?

 

[Uncle Gizmo]

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

Easily Apply Filters - Nifty Access

Filtering - MS Access Forms This video should be of interest to anyone who wants to easily apply filters to an MS Access form in datasheet view or continuous form view. In this demonstration I show how you can apply filters in a Form presented in Continuous view and also in Datasheet view. When you

www.niftyaccess.com

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​

 

[Pat Hartman]

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]

Kindly, I am requesting anyone to assist me with a sample database with OpenReport method. Thanks. Merry Christmas and Happy New year everybody.

 

[June7]

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]

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]

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