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

[HowHow]

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

Hi Jim,
Yes, I placed the calculated control on the report header. When I placed in other places, it won't work...It just shows #Error

I retried and re-read many times since yesterday and finally I know what goes wrong! Sorry I had mislead you with my previous question! I missed to point out this to you and this is very important! Each program is different!
For example, DC (Day Centre) program, we have shopping, outing-weekend, outing-weekday, young people with disability, transport, dementia, aboriginal group..etc. That is why when I choose *DC*, it chose all the clients under Day Centre including "DC-Shopping", "DC-Transport", "DC-Dementia"...etc . This is consider as DC program only but at the same time, they group up differently because they named differently.
In *HACC*, I have HACC-Domestic Assistance, HACC-Social Support, HACC-Transport, HACC-Gardening, HACC-Personal Care...etc. It is only HACC program but it will group differently if I show [Program] because the name is different.

That is why, at first, when I use a query named q_DC_ClientHvSv c. It works.In that query, I have [client name] and [Program] only. Under [Program] field, I use WHERE in "total" and type like *DC* in "criteria". Under [client name], I just have GROUP in "total". Result is, I have 121 client name listed. I created a report using that query and get the correct name list. With this method, I created few more queries, each for one program. And each program here means including different name within that program. I can get the name list for each program correctly because the [program] itself is not showing thus not group by program.

Now, I created a standart query. In that query, I have [client name] and [Program]. I created report using this new query but filter thru the button (which you showed me how to do earlier) so that each button will get the client name list from each program. When I click on the button that takes *DC* client, the client name list is 158 instead of 121 because some of the clients received more than one "activity" in one program. In the report, some of the client name repeated twice or three time depending on the "activity" they receive even though it is still in one program.

If I don't show [Program] field in my query and report, I cannot filter the [Program] using the button from my form. However, when I have [Program] in my query and in my report, my client name list is wrong.

Is there anyway to show or count client's name only once regardless of the "activity" they receive in the program? What should I do next?

[Jim Doherty]

Hi Jim,
Yes, I placed the calculated control on the report header. When I placed in other places, it won't work...It just shows #Error

I retried and re-read many times since yesterday and finally I know what goes wrong! Sorry I had mislead you with my previous question! I missed to point out this to you and this is very important! Each program is different!
For example, DC (Day Centre) program, we have shopping, outing-weekend, outing-weekday, young people with disability, transport, dementia, aboriginal group..etc. That is why when I choose *DC*, it chose all the clients under Day Centre including "DC-Shopping", "DC-Transport", "DC-Dementia"...etc . This is consider as DC program only but at the same time, they group up differently because they named differently.
In *HACC*, I have HACC-Domestic Assistance, HACC-Social Support, HACC-Transport, HACC-Gardening, HACC-Personal Care...etc. It is only HACC program but it will group differently if I show [Program] because the name is different.

That is why, at first, when I use a query named q_DC_ClientHvSv c. It works.In that query, I have [client name] and [Program] only. Under [Program] field, I use WHERE in "total" and type like *DC* in "criteria". Under [client name], I just have GROUP in "total". Result is, I have 121 client name listed. I created a report using that query and get the correct name list. With this method, I created few more queries, each for one program. And each program here means including different name within that program. I can get the name list for each program correctly because the [program] itself is not showing thus not group by program.

Now, I created a standart query. In that query, I have [client name] and [Program]. I created report using this new query but filter thru the button (which you showed me how to do earlier) so that each button will get the client name list from each program. When I click on the button that takes *DC* client, the client name list is 158 instead of 121 because some of the clients received more than one "activity" in one program. In the report, some of the client name repeated twice or three time depending on the "activity" they receive even though it is still in one program.

If I don't show [Program] field in my query and report, I cannot filter the [Program] using the button from my form. However, when I have [Program] in my query and in my report, my client name list is wrong.

Is there anyway to show or count client's name only once regardless of the "activity" they receive in the program? What should I do next?

Hi How How,

This can doubtless be sorted and no need to apologise you like all of us can easily make mistakes, however descriptively speaking on here you have lost me I'm afraid. If you PM (private message) me with your email address we can communicate offline and maybe you can send me an empty copy of your database and I'll take a look at it.

There may be a simple adjustment of design that can be had to return what you need but I'm afraid without looking at it my end I'm blind somewhat.

Regards

Jim :)

[HowHow]

Hi How How,

This can doubtless be sorted and no need to apologise you like all of us can easily make mistakes, however descriptively speaking on here you have lost me I'm afraid. If you PM (private message) me with your email address we can communicate offline and maybe you can send me an empty copy of your database and I'll take a look at it.

There may be a simple adjustment of design that can be had to return what you need but I'm afraid without looking at it my end I'm blind somewhat.

Regards

Jim :)

Thank you! Finally I got it! The Function works fine and my mistake was didn't take out the date, and service type...etc.
Only after I removed every field, left only [client name] and [ProgramGroup], it grouped up as what I need. Thanks again Jim! You are very kind and helpful!

Just to share the function with others if they need it.

Public Function ProgramType(strProgram As String)

On Error Resume Next
If InStr(strProgram, "-DC-") > 0 Then
ProgramType = "DC"
End If

' Group the program that has the word *DC* to DC

End Function

And in query, call the function by typing the below in field:

ProgramType: ProgramType([Program])