Filter report based on values of calculated value on report

[mlcampeau]

I have a report (JobVacanciesOn ly) that has a subreport (JobVacanciesOn lySR) that are based on two separate queries.
MY - JobVacancyJobs

SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc
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
ORDER BY Job.Title, Job.Code;

MY - JobVacancies

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
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] =

")))

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]!

 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!

[puppydogbuddy]

I have a report (JobVacanciesOn ly) that has a subreport (JobVacanciesOn lySR) that are based on two separate queries.
MY - JobVacancyJobs

SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc
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
ORDER BY Job.Title, Job.Code;

MY - JobVacancies

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
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] =

")))

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]!

 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

[mlcampeau]

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:

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
End Sub

but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies

[puppydogbuddy]

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:

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
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:

Private Sub Report_Activate()
Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
End Sub

[mlcampeau]

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:

Private Sub Report_Activate()
Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
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....

[puppydogbuddy]

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:

Private Sub Report_Open(Cancel As Integer)
Dim strWhere As String
strWhere = "[NumVacancies] > " & 0 & " or [TotalVacancies] > " & 0
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
End Sub
 

[mlcampeau]

Unfortunately it's still asking me to enter the parameter values :(

[puppydogbuddy]

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?

[mlcampeau]

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.

[puppydogbuddy]

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