How to filter report using button in Access 2000?

[HowHow]

I need to create a few reports using one query and I wish to do the program filter from the buttons in a form. I had created a query named q_ClientsHvServ ices and a report based on that query named Rpt_ClientsHvSe rvice. In the main form, I created few buttons to view clients from different progrom. When I click on a button named "HACC", the report should select all the clients that have services under PROGRAM starting with HA (and all HA*). When I click on a button named "CACP", the report should select all the clients starting with "CAC" program. Is it possible?

How should I write the filter based on the click on the buttons?

My code on my "All client" button are as follow:

Private Sub btn_OpenALLClientsHvSvc_Click()
On Error GoTo Err_btn_OpenALLClientsHvSvc_Click
 
    Dim stDocName As String
 
    stDocName = "Rpt_ClientsHvService"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_btn_OpenALLClientsHvSvc_Click:
    Exit Sub
 
Err_btn_OpenALLClientsHvSvc_Click:
    MsgBox Err.Description
    Resume Exit_btn_OpenALLClientsHvSvc_Click
 
End Sub

[Jim Doherty]

I need to create a few reports using one query and I wish to do the program filter from the buttons in a form. I had created a query named q_ClientsHvServ ices and a report based on that query named Rpt_ClientsHvSe rvice. In the main form, I created few buttons to view clients from different progrom. When I click on a button named "HACC", the report should select all the clients that have services under PROGRAM starting with HA (and all HA*). When I click on a button named "CACP", the report should select all the clients starting with "CAC" program. Is it possible?

How should I write the filter based on the click on the buttons?

My code on my "All client" button are as follow:

Private Sub btn_OpenALLClientsHvSvc_Click()
On Error GoTo Err_btn_OpenALLClientsHvSvc_Click
 
Dim stDocName As String
 
stDocName = "Rpt_ClientsHvService"
DoCmd.OpenReport stDocName, acPreview
 
Exit_btn_OpenALLClientsHvSvc_Click:
Exit Sub
 
Err_btn_OpenALLClientsHvSvc_Click:
MsgBox Err.Description
Resume Exit_btn_OpenALLClientsHvSvc_Click
 
End Sub

You need to build your criteria string for the WHERE clause of the openreport argument so that it knows the records to return as part of the report

 
Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Rpt_ClientsHvService"
 
stLinkCriteria = "[PROGRAM] Like 'HACC*'"
 
DoCmd.OpenReport stDocName, acPreview, , stWhere
 

I am sure you can adapt this to fit your fieldnames and other requirements .

For versatility you might want to look at referencing a selectable value from a form control as well, because hard coded like this it only does ONE thing (which may suit your purpose of course I don't know)

Regards

Jim :)

[HowHow]

You need to build your criteria string for the WHERE clause of the openreport argument so that it knows the records to return as part of the report

 
Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Rpt_ClientsHvService"
 
stLinkCriteria = "[PROGRAM] Like 'HACC*'"
 
DoCmd.OpenReport stDocName, acPreview, , stWhere
 

I am sure you can adapt this to fit your fieldnames and other requirements .

For versatility you might want to look at referencing a selectable value from a form control as well, because hard coded like this it only does ONE thing (which may suit your purpose of course I don't know)

Regards

Jim :)

I still can't get it right. Is it because I don't have [Program] in my report? Does the code control the [Program] on Query?

My purpose is to know the number of the client received service from particular program and get a client name list from that result. If I put [Program] to my Report, the client numbers will be more than what I expected because it group by program and then by client's name. Well, I had tried to put [Program] to report and tested on the code, the report gave all the clients from all the program. The code is as below, can you spot where goes wrong?

Private Sub btn_OpenALLClientHasSvc_Click()
On Error GoTo Err_btn_OpenALLClientHasSvc_Click
 
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Rpt_ClientHasService"
    stLinkCriteria = "[Program] like 'HA*' "
 
    DoCmd.OpenReport stDocName, acPreview, , stWhere
 
Exit_btn_OpenALLClientHasSvc_Click:
    Exit Sub
 
Err_btn_OpenALLClientHasSvc_Click:
    MsgBox Err.Description
    Resume Exit_btn_OpenALLClientHasSvc_Click
 
End Sub

Previously I created query for each program, then created reports based on the queries. It is accurate but I found too many queries and reports to maintan everytime something changed. So I am thinking of using one standard Query and one standard Report but control the program filter in Query thru button that has Program caption in Form.

What should I do to control the filter in Query from Form (button) so that I will get the correct Report? Is it possible?

[Jim Doherty]

I still can't get it right. Is it because I don't have [Program] in my report? Does the code control the [Program] on Query?

My purpose is to know the number of the client received service from particular program and get a client name list from that result. If I put [Program] to my Report, the client numbers will be more than what I expected because it group by program and then by client's name. Well, I had tried to put [Program] to report and tested on the code, the report gave all the clients from all the program. The code is as below, can you spot where goes wrong?

Private Sub btn_OpenALLClientHasSvc_Click()
On Error GoTo Err_btn_OpenALLClientHasSvc_Click
 
 
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Rpt_ClientHasService"
stLinkCriteria = "[Program] like 'HA*' "
 
DoCmd.OpenReport stDocName, acPreview, , stWhere
 
Exit_btn_OpenALLClientHasSvc_Click:
Exit Sub
 
Err_btn_OpenALLClientHasSvc_Click:
MsgBox Err.Description
Resume Exit_btn_OpenALLClientHasSvc_Click
 
End Sub

Previously I created query for each program, then created reports based on the queries. It is accurate but I found too many queries and reports to maintan everytime something changed. So I am thinking of using one standard Query and one standard Report but control the program filter in Query thru button that has Program caption in Form.

What should I do to control the filter in Query from Form (button) so that I will get the correct Report? Is it possible?

Hi HowHow :)

With the greatest of respect read your original post you said

"select all the clients that have services under PROGRAM starting with HA"

From that I can only assume that PROGRAM is a fieldname in your table or query and that it is this that you are going to compare in returning a dataset where the PROGRAM contains data starting with HA...hence the LIKE operand.

The logic in all of this is to return datasets based on comparisons you make against fielded data using the WHERE clause of an SQL statement.

So......

SELECT * FROM tblMyData WHERE [Surname]='Jones'"
returns everyone who has a surname of Jones

SELECT * FROM YourTableName WHERE [Program]='Rusty Car' AND [Service]='15,000,000 miles'
returns all cars that either need scrapping or awarding a congratulatory certificate of longevity.

The code displayed for the report is correct! in that it 'would' open a report where and IF a field existed on there called PROGRAM and where the field starts with HA.

You build your reports around your tables or queries and you call your reports restricting any data it returns by using the WHERE clause of the openreport method as displayed

Query by FORM or QBF as it is known in short terms from your perspective is an accepted method of calling a report and supplying criteria to it from FORM controls where you have picked or entered your criteria into the form (usually unbound) upon which the report will rely when it is opened.

If this is what you are wanting then yes it can be done. there are lots of references to the query by form methodology for you to research

Jim :)

[HowHow]

Hi HowHow :)

With the greatest of respect read your original post you said

"select all the clients that have services under PROGRAM starting with HA"

From that I can only assume that PROGRAM is a fieldname in your table or query and that it is this that you are going to compare in returning a dataset where the PROGRAM contains data starting with HA...hence the LIKE operand.

The logic in all of this is to return datasets based on comparisons you make against fielded data using the WHERE clause of an SQL statement.

So......

SELECT * FROM tblMyData WHERE [Surname]='Jones'"
returns everyone who has a surname of Jones

SELECT * FROM YourTableName WHERE [Program]='Rusty Car' AND [Service]='15,000,000 miles'
returns all cars that either need scrapping or awarding a congratulatory certificate of longevity.

The code displayed for the report is correct! in that it 'would' open a report where and IF a field existed on there called PROGRAM and where the field starts with HA.

You build your reports around your tables or queries and you call your reports restricting any data it returns by using the WHERE clause of the openreport method as displayed

Query by FORM or QBF as it is known in short terms from your perspective is an accepted method of calling a report and supplying criteria to it from FORM controls where you have picked or entered your criteria into the form (usually unbound) upon which the report will rely when it is opened.

If this is what you are wanting then yes it can be done. there are lots of references to the query by form methodology for you to research

Jim :)

Hi Jim :),
Sorry for the unclear question previously.You are right that [Program] is in my table as well as in the query, however, it wasn't show in my report because I have "WHERE" in my query and I cannot tick "show" box.

Please forget about my previous set up. Now I had created a new query (called q_RA_unapproved ), and a new report (called rpt_RA_unapprov ed) based on that query, both has [Program] in it. In the form, I created unbound button where I want to view "rpt_RA_unappro ved" by program. However, the code below still doesn't work. It brings all the unapproved service from all the programs instead of the specific program I want.

Private Sub btn_CACP_RA_unapproved_Click()
On Error GoTo Err_btn_CACP_RA_unapproved_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "rpt_RA_unapproved"
    stLinkCriteria = "[Program] like ' *C.A.C* ' "
    DoCmd.OpenReport stDocName, acPreview, , stWhere
 
 
Exit_btn_CACP_RA_unapproved_Click:
    Exit Sub
 
Err_btn_CACP_RA_unapproved_Click:
    MsgBox Err.Description
    Resume Exit_btn_CACP_RA_unapproved_Click
 
End Sub

Can you help spot where goes wrong? appreciate your time... :)

[Jim Doherty]

Hi Jim :),
Sorry for the unclear question previously.You are right that [Program] is in my table as well as in the query, however, it wasn't show in my report because I have "WHERE" in my query and I cannot tick "show" box.

Please forget about my previous set up. Now I had created a new query (called q_RA_unapproved ), and a new report (called rpt_RA_unapprov ed) based on that query, both has [Program] in it. In the form, I created unbound button where I want to view "rpt_RA_unappro ved" by program. However, the code below still doesn't work. It brings all the unapproved service from all the programs instead of the specific program I want.

Private Sub btn_CACP_RA_unapproved_Click()
On Error GoTo Err_btn_CACP_RA_unapproved_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 
stDocName = "rpt_RA_unapproved"
stLinkCriteria = "[Program] like ' *C.A.C* ' "
DoCmd.OpenReport stDocName, acPreview, , stWhere
 
 
Exit_btn_CACP_RA_unapproved_Click:
Exit Sub
 
Err_btn_CACP_RA_unapproved_Click:
MsgBox Err.Description
Resume Exit_btn_CACP_RA_unapproved_Click
 
End Sub

Can you help spot where goes wrong? appreciate your time... :)

Read your code strLinkCriteria is not being passed to the where clause of the openreport command you have

DoCmd.OpenRepor t stDocName, acPreview, , stWhere

it should be

DoCmd.OpenRepor t stDocName, acPreview, , stLinkCriteria

You created criteria yes and assigned a variable to it but are not passing it the the command correctly

Regards

Jim

[HowHow]

Read your code strLinkCriteria is not being passed to the where clause of the openreport command you have

DoCmd.OpenRepor t stDocName, acPreview, , stWhere

it should be

DoCmd.OpenRepor t stDocName, acPreview, , stLinkCriteria

You created criteria yes and assigned a variable to it but are not passing it the the command correctly

Regards

Jim

Thank you! This is a big help! Now I can stop creating lots of queries and reports (for each program) now! yeah!

I guess I have no way to do the same if the field is not in the report even though the field [Program] is in my query and table. When I put the [Program] to the report, the total number of client is wrong because a client can have more than one program. I only want to have each client counted once and produce a non duplicate name list.

[Jim Doherty]

Thank you! This is a big help! Now I can stop creating lots of queries and reports (for each program) now! yeah!

I guess I have no way to do the same if the field is not in the report even though the field [Program] is in my query and table. When I put the [Program] to the report, the total number of client is wrong because a client can have more than one program. I only want to have each client counted once and produce a non duplicate name list.

Group your Report by Client in the report itself. Look at Report sorting and grouping

Jim

[HowHow]

Group your Report by Client in the report itself. Look at Report sorting and grouping

Jim

I had tried few times but still didn't get it. Even recreate my query and report...Really lost already!
When I have [Program] in both of my query and report, the report is group by both [Program] and client's name. For example, when a client received 3 programs many times from Jan to June, the report show client's name three times and each with a program beside (different program). Thus my text box with

=Count([Name]) 

total up the same client as 3 instead of 1. My name list should be one client but end up with 3 same name. Even after I make the [Program] invisible in report, it still does the same thing. Need your advise again please...thanks !

[Jim Doherty]

I had tried few times but still didn't get it. Even recreate my query and report...Really lost already!
When I have [Program] in both of my query and report, the report is group by both [Program] and client's name. For example, when a client received 3 programs many times from Jan to June, the report show client's name three times and each with a program beside (different program). Thus my text box with

=Count([Name]) 

total up the same client as 3 instead of 1. My name list should be one client but end up with 3 same name. Even after I make the [Program] invisible in report, it still does the same thing. Need your advise again please...thanks !

If you are grouping by client do you have the headers and footers displayed in your report for the client? if not do so and place any calculated control within the header or footer, that way your count function will count on that grouping only if I am reading you correctly.

Regards

Jim