Apply Filter to A Report

[Nhmiller]

I already have a report designed under Reports. When I use filtering in Forms
or Tables, I see no way to select that filtered list when I am in Reports, and
there appears to be no way to do the filtering when I am in Reports.

I have received two replies to this message, posted a few days ago within
another Subject. One requires making programming type statements. That should
not be necessary -- Access must have a quick way to just click on something to
apply the filter to my already formatted report layout. The other reply said to
base the report on a query -- it appears to me I can only do that if I then
create a new report.

Thanks to anyone who can give me the simple instructions that must be in
Access, but sure are not in the Help menus.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[fredg]

On 11 Nov 2004 19:52:27 GMT, Nhmiller wrote:

I already have a report designed under Reports. When I use filtering in Forms
or Tables, I see no way to select that filtered list when I am in Reports, and
there appears to be no way to do the filtering when I am in Reports.

I have received two replies to this message, posted a few days ago within
another Subject. One requires making programming type statements. That should
not be necessary -- Access must have a quick way to just click on something to
apply the filter to my already formatted report layout. The other reply said to
base the report on a query -- it appears to me I can only do that if I then
create a new report.

Thanks to anyone who can give me the simple instructions that must be in
Access, but sure are not in the Help menus.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

Are you using a form, and filtering the records using the "Filter by
Selection" or "Filter by Form" tool buttons?

If so, Add a command button to the form.
Code it's Click event:
DoCmd.OpenReport "ReportName", acViewPreview , , Me.Filter

Filter the records on the form, then click the button.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Nhmiller]

>Are you using a form, and filtering the records using the "Filter by

Selection" or "Filter by Form" tool buttons?
Yes, Filter By Selection

If so, Add a command button to the form.
Code it's Click event:
DoCmd.OpenReport "ReportName", acViewPreview , , Me.Filter
My report is called Roster. Do I just substitute "Roster" for "ReportName"? I
tried that and nothing happened after I continued as below:

Filter the records on the form, then click the button.

Do you know what I did wrong? Also, how would anyone know to use a statement
like that? I'm surprised Microsoft does not have an easy way to accomplish this
task.

Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

>Are you using a form, and filtering the records using the "Filter by

Selection" or "Filter by Form" tool buttons?
Yes, Filter By Selection

If so, Add a command button to the form.
Code it's Click event:
DoCmd.OpenReport "ReportName", acViewPreview , , Me.Filter
My report is called Roster. Do I just substitute "Roster" for "ReportName"? I
tried putting the statement in the Event Tab but the characters wouldn't
appear.

Filter the records on the form, then click the button.

Do you know what I did wrong? Also, how would anyone know to use a statement
like that? I'm surprised Microsoft does not have an easy way to accomplish this
task. Perhaps if you could instruct me on this with me using the Wizard button
in conjunction with placing the Command button in my form, that might be
easier.

Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[fredg]

On 11 Nov 2004 21:41:35 GMT, Nhmiller wrote:

Are you using a form, and filtering the records using the "Filter by
Selection" or "Filter by Form" tool buttons?

Yes, Filter By Selection

If so, Add a command button to the form.
Code it's Click event:
DoCmd.OpenReport "ReportName", acViewPreview , , Me.Filter

My report is called Roster. Do I just substitute "Roster" for "ReportName"? I
tried putting the statement in the Event Tab but the characters wouldn't
appear.

Filter the records on the form, then click the button.

** snipped **

When you have added the command button to the form, display it's
property sheet.
Click on the Event tab.
On the Click line write:
[Event Procedure]
Then click on the button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenReport "Roster", acViewPreview , , Me.Filter

Exit the code window and save the changes.
Open the Form. Filter the records using one of the filter tool
buttons. Click the command button.
The Roster report will open, with just the filtered records.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Nhmiller]

>** snipped **

When you have added the command button to the form, display it's
property sheet.
Click on the Event tab.
On the Click line write:
[Event Procedure]
Then click on the button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenReport "Roster", acViewPreview , , Me.Filter

Exit the code window and save the changes.
Open the Form. Filter the records using one of the filter tool
buttons. Click the command button.
The Roster report will open, with just the filtered records.

Thank you. That worked. I gather there are several ways of accomplishing this
same task. An earlier reply on another Subject said to do it through a query.
What I am concerned about is how does a user, who expects Windows software to
be user friendly, know how to create code like yours? For comparison, the 1994
version of Lotus Approach requires nothing like this -- all tasks are done from
commands in dropdown menus, and apply universally to all views of a database.
Could you explain to me why Access is so code oriented? When I read the Help
topics, I have no clue what they are talking about or how to do what they are
saying. And I am a software engineer, though only as part of my work and on a
lower level.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Salad]

Nhmiller wrote:

** snipped **

When you have added the command button to the form, display it's
property sheet.
Click on the Event tab.
On the Click line write:
[Event Procedure]
Then click on the button with 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenReport "Roster", acViewPreview , , Me.Filter

Exit the code window and save the changes.
Open the Form. Filter the records using one of the filter tool
buttons. Click the command button.
The Roster report will open, with just the filtered records.

Thank you. That worked. I gather there are several ways of accomplishing this
same task. An earlier reply on another Subject said to do it through a query.
What I am concerned about is how does a user, who expects Windows software to
be user friendly, know how to create code like yours? For comparison, the 1994
version of Lotus Approach requires nothing like this -- all tasks are done from
commands in dropdown menus, and apply universally to all views of a database.
Could you explain to me why Access is so code oriented?

Flexibility. Expandability.

Many tasks are repetitious. Many aren't.
When I read the Help topics, I have no clue what they are talking about or how to do what they are
saying. And I am a software engineer, though only as part of my work and on a
lower level.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

>DoCmd.OpenReport "Roster", acViewPreview , , Me.Filter

Would you be able to tell me where in Access (Help or otherwise) there is an
explanation of this command, and if there is a Help section with instructions
about writing code for the various needs you have run into? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Sunil Korah]

nh******@aol.comnojunk (Nhmiller) wrote in message news:<20***************************@mb-m25.aol.com>...

I already have a report designed under Reports. When I use filtering in Forms
or Tables, I see no way to select that filtered list when I am in Reports, and
there appears to be no way to do the filtering when I am in Reports.

I have received two replies to this message, posted a few days ago within
another Subject. One requires making programming type statements. That should
not be necessary -- Access must have a quick way to just click on something to
apply the filter to my already formatted report layout. The other reply said to
base the report on a query -- it appears to me I can only do that if I then
create a new report.

Thanks to anyone who can give me the simple instructions that must be in
Access, but sure are not in the Help menus.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

The other reply said to base the report on a query -- it appears to
me I can only do that if I then create a new report.

Not so. Create the query. Then change the 'Record Source' property of
the existing report to the name of the query.

HTH

Sunil Korah

[Nhmiller]

>Create the query. Then change the 'Record Source' property of

the existing report to the name of the query.

I created the query using the query wizard. I selected all the fields, then did
a filter by selection on one of the fields. When I go to the report tab and
right click on the report name, then click on properties, there is just a
general tab and a window to enter descriptive information. Where is Record
Source? Thanks.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Salad]

Nhmiller wrote:

DoCmd.OpenReport "Roster", acViewPreview , , Me.Filter

Would you be able to tell me where in Access (Help or otherwise) there is an
explanation of this command, and if there is a Help section with instructions
about writing code for the various needs you have run into? Thanks.

Well...versions after A97 use a Help-less system. It's slow, impossible
to use, obtuse, unsorted, and generally considered crap by anyone with
an education above kindergarten. However, it is promoted by MS. It's
possible they are doing this to help in the sales of computer books.
It's also possible that the project managers at MS are satisfied with
second rate products or that few people ever complained to MS that their
help system is worth shit.

That being said, you should be able to search and find Docmd. Also,
OpenReport. or OpenForm. Be prepared to work harder and longer to find
information in the MS Help-less system. (How can a MS
help-documentation worker have any pride in this crap?)

If you can't find it in the MS Help-less (tm) system, you may need to
purchase/read a book on it. Quite a few books on Office are worthless too.


Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Bas Cost Budde]

Nhmiller wrote:

Create the query. Then change the 'Record Source' property of
the existing report to the name of the query.

I created the query using the query wizard. I selected all the fields, then did
a filter by selection on one of the fields. When I go to the report tab and
right click on the report name, then click on properties, there is just a
general tab and a window to enter descriptive information. Where is Record
Source? Thanks.

You are looking at the properties of the report *document*. To see the
properties of the report *object*, it has to be open, in design view.
Click the Design button to do that.

I suggest you realize Access is difficult to use until you get a grasp
of its object model, and where to look for what. To get such a grasp,
digging into an existing application is a great step.

What version do you use? 97?

[Nhmiller]

>Nhmiller wrote:

Create the query. Then change the 'Record Source' property of
the existing report to the name of the query.

I created the query using the query wizard. I selected all the fields, then

did

a filter by selection on one of the fields. When I go to the report tab and
right click on the report name, then click on properties, there is just a
general tab and a window to enter descriptive information. Where is Record
Source? Thanks.

You are looking at the properties of the report *document*. To see the
properties of the report *object*, it has to be open, in design view.
Click the Design button to do that.

I had tried that before, but now I see I have to right-click on the blue title
bar of the design window for the Properties to come up. As usual, the Help
menus do not tell you this -- Access' Help system is terrible. Once I am in the
Properties, I tried using the name of the query in Record Source. The problem
is I must not have created the Query correctly. In the Query section, a table
appears, the same as if I go to Table. I then did a Filter on Selection, but I
don't know how to save that so that when I go to Reports and enter the name of
the query in Record Source, I get the filtered records. Appreciate you
explaining what to do next. Thank you.


I suggest you realize Access is difficult to use until you get a grasp
of its object model, and where to look for what. To get such a grasp,
digging into an existing application is a great step.
Yes, I'm doing that, too. But, it's obvious that using the Help menus is of
very little use -- they are highly technical and code-oriented without any
explanations of how to learn and use the code. There should be a tutorial
and/or detailed examples of how to do at least the common operations. I am
surprised Microsoft gets away with this. I also have two books on Access
requested at our library -- I'll have those next week.

What version do you use? 97?

Office XP, which I think is also called Office 2002.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Nhmiller]

>Nhmiller wrote:

Create the query. Then change the 'Record Source' property of
the existing report to the name of the query.

I created the query using the query wizard. I selected all the fields, then

did

a filter by selection on one of the fields. When I go to the report tab and
right click on the report name, then click on properties, there is just a
general tab and a window to enter descriptive information. Where is Record
Source? Thanks.

You are looking at the properties of the report *document*. To see the
properties of the report *object*, it has to be open, in design view.
Click the Design button to do that.

I had tried that before, but now I see I have to right-click on the blue title
bar of the design window for the Properties to come up. As usual, the Help
menus do not tell you this -- Access' Help system is terrible. I was able to
select the query, then change the No to a Yes to use the query, and it all
worked. Your method is better than another one described earlier in this
thread, as yours does not require any programming or coding -- the filter I use
in Query is saved and named, and available to use in the Report. Thank you.

I suggest you realize Access is difficult to use until you get a grasp
of its object model, and where to look for what.
It's only difficult to use because the Help menus give no explanation of how to
do the most simple tasks. I assume you learned how to do this from an earlier
version that had better Help, or from your own research, as I am doing.
To get such a grasp,
digging into an existing application is a great step.
Yes, I'm doing that, too. But, it's obvious that using the Help menus is of
very little use -- they are highly technical and code-oriented without any
explanations of how to learn and use the code. There should be a tutorial
and/or detailed examples of how to do at least the common operations. I am
surprised Microsoft gets away with this. I also have two books on Access
requested at our library -- I'll have those next week.

What version do you use? 97?

Office XP, which I think is also called Office 2002.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Bas Cost Budde]

Nhmiller wrote:

I had tried [viewing properties] before, but now I see I have to right-click
on the blue title
bar of the design window for the Properties to come up.
That is not the only way! There is a Properties item in the View menu;
there is a Properties button on the toolbar.
As usual, the Help
menus do not tell you this -- Access' Help system is terrible.
I suggest you examine whether your bad experience so far gets in the way
of your ability to look around. I must be honest to say I never have
used Access XP (so far) and hence I don't know a thing about its Help.
My experience with Access 97 is fine.

Maybe Microsoft documentation is not your thing. Learn it, then. I
understand you are seasoned in other environments, and I ensure you this
can be done.

I get the hint that you don't like the information sources, so far.
Please do not further mention this.

0> Once I am in the Properties, I tried using the name of the query in Record Source. The problem
is I must not have created the Query correctly. In the Query section, a table
appears, the same as if I go to Table. I then did a Filter on Selection, but I
don't know how to save that so that when I go to Reports and enter the name of
the query in Record Source, I get the filtered records. Appreciate you
explaining what to do next. Thank you.
A query is basically a stored SQL statement. For reports, the only
useful statement is SELECT because that returns records.
Such a query can contain any filter. When you have the query in Design
view, the filtering is displayed as criteria line(s). I believe the
interface is "Query By Example". When you have the query in SQL view,
the filtering is in the WHERE clause.

How familiar are you with SQL, actually?

To use a filter, you enter the corresponding expression in the correct
place, save the query--probably using a different name to indicate what
the query filters--and use that as recordsource for the report. I take
it you have noticed you can choose the recordsource from a dropdown list.
[...] using the Help menus is of very little use -- they are highly technical
and code-oriented without any
explanations of how to learn and use the code.
In my experience there are two environments of Help. The one is about
using Access, forms, controls, and whatsoever; the other is VBA help, so
for all coding issues. I do find the former helpful, whenever I don't
know exactly how to do something. To get the basic understanding,
however, one needs more information. You must have some general idea
where to go.
I am surprised Microsoft gets away with this.

That is all marketing.

Now, how about that Northwind sample I keep nagging about?

[Nhmiller]

I revised the message you responded to, as I was successful using query and
report. I stated that your method works without resorting to codes constructed
by the user -- that's the way software should work. The old message must have
made it through to your newsgroup site anyway. I think it is important that we
express our opinions about product improvement -- Microsoft could have made
their Help section far better, but chose not to. One contributor to this thread
said it's because they want to sell books on using Office. If they are being
greedy, I think we should let them know. I'm lucky there's people like you
answering questions on newsgroups. I now have a good feel for how Access works.
Thank you.

Neil
Cat Paintings At Carol Wilson Gallery
http://www.carolwilsongallery.com

[Bas Cost Budde]

Nhmiller wrote:

I revised the message you responded to, as I was successful using query and
report. I stated that your method works without resorting to codes constructed
by the user -- that's the way software should work. The old message must have
made it through to your newsgroup site anyway.
Yes, I saw there was a second post. Only after I sent my reply.
I think it is important that we
express our opinions about product improvement -- Microsoft could have made
their Help section far better, but chose not to. One contributor to this thread
said it's because they want to sell books on using Office. If they are being
greedy, I think we should let them know.
Mmm, don't get me wrong: I fully agree. I suspect, though, that future
versions of Access will *not* contain all the good advice that the share
of users, among who those that visit these newsgroups, collects. Some
*does*. And there's no crying over spilled milk, version XP was released.

I'm lucky there's people like you answering questions on newsgroups. I now have a good feel for how Access works.
Thank you.

Any time, keep asking until everything works.