473,946 Members | 12,171 Online

# Filter report based on values of calculated value on report

296 Recognized Expert Contributor
I have a report (JobVacanciesOn ly) that has a subreport (JobVacanciesOn lySR) that are based on two separate queries.
MY - JobVacancyJobs
Expand|Select|Wrap|Line Numbers
2. FROM Grade RIGHT JOIN (EEOSubCategory RIGHT JOIN (EEOCategory RIGHT JOIN Job ON EEOCategory.Code = Job.EEOCategoryCode) ON EEOSubCategory.Code = Job.EEOSubCategoryCode) ON Grade.Code = Job.Grade
3. ORDER BY Job.Title, Job.Code;
MY - JobVacancies
Expand|Select|Wrap|Line Numbers
1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
3. GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
4. ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;
There is a textbox on the main report that calculates the total number of vacancies for each job position
[code]TotalVacancies= IIf(((DSum("[NumberofPositio ns]","JobVacan cy", "[JobVacancy.JobC ode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =[code]"))<0,0,((DSum( "[NumberofPositio ns]","JobVacan cy", "[JobVacancy.JobC ode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =
Expand|Select|Wrap|Line Numbers
1. ")))
and a textbox in the subreport that calculates the number of vacancies for that job position, in a specific department and division
[code]NumVacancies=II f([NumberofPositio ns]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnl y]![code] AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] "))<0,0,[NumberofPositio ns]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnl y]!
Expand|Select|Wrap|Line Numbers
1.  AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] ")))
What I want to do now, is filter my report so that when it opens, it only shows the records that have NumVacancies or TotalVacancies with a value greater than 0 (basically I want it to only display the records where there is a job vacancy). It would seem that the easiest method would be to filter this in a query, but after over a week of trying to get a query to calculate the vacancies for me, it seems that with my dataset, it is not possible to get the desired results - hence the 2 queries above. So......is there any way to filter this based on a calculated value textbox that is on the report?? My VBA is very very limited, so please don't oversimplify if that's what I need to be using. Thanks in advance!
Jul 27 '07 #1
94 6998
puppydogbuddy
1,923 Recognized Expert Top Contributor
I have a report (JobVacanciesOn ly) that has a subreport (JobVacanciesOn lySR) that are based on two separate queries.
MY - JobVacancyJobs
Expand|Select|Wrap|Line Numbers
2. FROM Grade RIGHT JOIN (EEOSubCategory RIGHT JOIN (EEOCategory RIGHT JOIN Job ON EEOCategory.Code = Job.EEOCategoryCode) ON EEOSubCategory.Code = Job.EEOSubCategoryCode) ON Grade.Code = Job.Grade
3. ORDER BY Job.Title, Job.Code;
MY - JobVacancies
Expand|Select|Wrap|Line Numbers
1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
3. GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
4. ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;
There is a textbox on the main report that calculates the total number of vacancies for each job position
[code]TotalVacancies= IIf(((DSum("[NumberofPositio ns]","JobVacancy", "[JobVacancy.JobC ode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =[code]"))<0,0,((DSum( "[NumberofPositio ns]","JobVacancy", "[JobVacancy.JobC ode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =
Expand|Select|Wrap|Line Numbers
1. ")))
and a textbox in the subreport that calculates the number of vacancies for that job position, in a specific department and division
[code]NumVacancies=II f([NumberofPositio ns]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnl y]![code] AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] "))<0,0,[NumberofPositio ns]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnl y]!
Expand|Select|Wrap|Line Numbers
1.  AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] ")))
What I want to do now, is filter my report so that when it opens, it only shows the records that have NumVacancies or TotalVacancies with a value greater than 0 (basically I want it to only display the records where there is a job vacancy). It would seem that the easiest method would be to filter this in a query, but after over a week of trying to get a query to calculate the vacancies for me, it seems that with my dataset, it is not possible to get the desired results - hence the 2 queries above. So......is there any way to filter this based on a calculated value textbox that is on the report?? My VBA is very very limited, so please don't oversimplify if that's what I need to be using. Thanks in advance!
Just incorporate your filter condition in the Where argument of the OpenReport command like the following:

Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenRepor t "YourReportName ", acViewPreview, , strWhere
Jul 27 '07 #2
mlcampeau
296 Recognized Expert Contributor
Thanks for the reply. As I said, I am very very new to VBA. I understand the concepts of the OpenReport command and the coding makes sense to me, but where do I put the code? Right now, I don't have a form with a button to open the report, I'm just opening it through the database window. Would it go in the OnOpen event of the report? I tried this:
Expand|Select|Wrap|Line Numbers
1. Private Sub Report_Open(Cancel As Integer)
2. Dim strWhere As String
3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
5. End Sub
but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies
Jul 27 '07 #3
puppydogbuddy
1,923 Recognized Expert Top Contributor
Thanks for the reply. As I said, I am very very new to VBA. I understand the concepts of the OpenReport command and the coding makes sense to me, but where do I put the code? Right now, I don't have a form with a button to open the report, I'm just opening it through the database window. Would it go in the OnOpen event of the report? I tried this:
Expand|Select|Wrap|Line Numbers
1. Private Sub Report_Open(Cancel As Integer)
2. Dim strWhere As String
3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
5. End Sub
but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies
If you are getting prompted to enter the parameters, you might try declaring the two parameters on the line preceding the beginning of the select statement that they appear so Access will know how to handle them and see if that helps: The syntax is illustrated below. Be sure and put their correct data type:

PARAMETERS [NumVacancies] Integer, [TotalVacancies] Integer;
Select xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxx

If that doesn't help:
Normally the OpenReport code is put behind a button, but it can be initiated from an event procedure using the report's OnOpen event like you did, except that I think the form open event might be too late to pass your parameters. It might work from the Form activate event, which precedes the form open event:

Expand|Select|Wrap|Line Numbers
1. Private Sub Report_Activate()
2. Dim strWhere As String
3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
5. End Sub
Jul 28 '07 #4
mlcampeau
296 Recognized Expert Contributor
If you are getting prompted to enter the parameters, you might try declaring the two parameters on the line preceding the beginning of the select statement that they appear so Access will know how to handle them and see if that helps: The syntax is illustrated below. Be sure and put their correct data type:

PARAMETERS [NumVacancies] Integer, [TotalVacancies] Integer;
Select xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxx
NumVacancies and TotalVacancies are not in any of my Select statements as they are calculated on the report in a textbox.

If that doesn't help:
Normally the OpenReport code is put behind a button, but it can be initiated from an event procedure using the report's OnOpen event like you did, except that I think the form open event might be too late to pass your parameters. It might work from the Form activate event, which precedes the form open event:

Expand|Select|Wrap|Line Numbers
1. Private Sub Report_Activate()
2. Dim strWhere As String
3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
5. End Sub
I tried placing the above code in the Activate event and it didn't ask me for parameters, but it also didn't filter anything out....
Jul 30 '07 #5
puppydogbuddy
1,923 Recognized Expert Top Contributor
NumVacancies and TotalVacancies are not in any of my Select statements as they are calculated on the report in a textbox.

I tried placing the above code in the Activate event and it didn't ask me for parameters, but it also didn't filter anything out....
Ok, put your code back in the Report Open event with the changed syntax for strWhere as shown below and let me know if that mad e a difference:
Expand|Select|Wrap|Line Numbers
1. Private Sub Report_Open(Cancel As Integer)
2. Dim strWhere As String
3. strWhere = "[NumVacancies] > " & 0 & " or [TotalVacancies] > " & 0
4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
5. End Sub
6.
Jul 30 '07 #6
mlcampeau
296 Recognized Expert Contributor
Unfortunately it's still asking me to enter the parameter values :(
Jul 30 '07 #7
puppydogbuddy
1,923 Recognized Expert Top Contributor
Unfortunately it's still asking me to enter the parameter values :(

Ok, what are the names of the textboxes on the report that are holding the calcs for NumVacancies and TotalVacancies?
Jul 30 '07 #8
mlcampeau
296 Recognized Expert Contributor
Ok, what are the names of the textboxes on the report that are holding the calcs for NumVacancies and TotalVacancies?
The names are NumVacancies and TotalVacancies. I have triple checked the names because this doesn't seem like it should be such a difficult thing to do. The format is General Number for both of those textboxes.
Jul 30 '07 #9
puppydogbuddy
1,923 Recognized Expert Top Contributor
The names are NumVacancies and TotalVacancies. I have triple checked the names because this doesn't seem like it should be such a difficult thing to do. The format is General Number for both of those textboxes.
change strWhere to this (for now, see if it works for one textbox):

strWhere = "Me![NumVacancies] > " & 0
Jul 30 '07 #10