Applying a filter to an existing report

[Dougbum]

As a new user of Access, I've created a number of forms for use in tracking medical equipment. After creating a report from the main "Contacts" table, one of the therapists has suggested that I narrow the report's scope to "Active" patients only. The main form has a field to track "patient status".

Since the report was not generated using a query, how can I apply a filter to the existing report? I have read most of the threads on this site, and on another Access website, with little success.

Any help will be GREATLY appreciated!

Doug

 

[pbaldy]

If you always want that restriction, I'd change the report to be based on a query that included that as a criteria. It would be a simple change. If not, you can restrict it as you open it with the wherecondition argument of OpenReport. Check it out in VBA Help.

 

[Dougbum]

Thanks Paul

Your help and quick reply is great.
I'll try the Wherecondition at OpenReport first, I'm not quite sure about applying a newly-created query to an existing report. It's probably VERY easy but I am a novice at this point.

Thanks again,
Doug

 

[pbaldy]

No problem; post back if you have trouble with either.

 

[Dougbum]

Still having problems!

Hi Paul,
I tried to build a VB code for my filter using the following on the On Open command in the Event Procedure;

Private Sub Report_Open(Cancel As Integer)
DoCmd.ApplyFilter "STATUS = 1"
End Sub

The error message says that the Jet Database can't find the object 'STATUS=1'.

Here's what I want to do...

My report is based on a table called Contacts (remember I didn't build off a query). The "STATUS" field is populated with either a 1 for "Active", 2 for "Inactive" or 3 for "Missing".
My report pulls ALL of the entries and lists them, but I would like to only list the "Active" records.

I've tried to build a query table for the report but had problems with that, so I've given up and decided to try the On Open approach.

Obviously, I need additional help with this seemingly simple problem.

Again, my thanks to you and any other brave souls who would like to teach this OLD dog some NEW tricks!!!

Doug

 

[pbaldy]

Like I said, the simplest solution if you always want it filtered that way is to base it on a query. It should be simple, but make a copy first in case it all goes horribly wrong. In the report properties, on the data tab, the Record Source will be your table. Click on the ellipsis (...) to the right. It may ask if you want to invoke the query builder; if so, say yes. It should open the query builder with the table already shown at the top. You can double-click on each field needed for the report so it displays below in the Field grid. Under the status field, in the Criteria box, put a 1. Close it all out and run the report and see if you get what you want.

 

[Dougbum]

It Worked!

Did as you suggested, it worked like a dream! I was even able to include data from a subform into the report!
My humble thanks to you for your timely help!!!

Best wishes,

Doug

 

[pbaldy]

No problem, Doug. Glad we got it sorted out.