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 meant subreport, not subform

Access processes the Open event before it runs the query that provides the report with its data, so I have not found any way to filter the report without creating an updated recordset from the report’s recordset that includes the effect of the parameters. Try this and let me know.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Routine
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef.
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("JobVacancyJobs")
    qdf.Parameters(1) = Me![TotalVacancies].Value > 0
    qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
    Set rs = qdf.OpenRecordset()
 
    rs.Close
 
DoCmd.Maximize
 
Exit_Continue:
    Set rs = Nothing
    Set qdf = Nothing
    Exit Sub
Error_Routine:
        MsgBox "Error " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
 

[mlcampeau]

Access processes the Open event before it runs the query that provides the report with its data, so I have not found any way to filter the report without creating an updated recordset from the report’s recordset that includes the effect of the parameters. Try this and let me know.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Routine
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef.
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("JobVacancyJobs")
    qdf.Parameters(1) = Me![TotalVacancies].Value > 0
    qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
    Set rs = qdf.OpenRecordset()
 
    rs.Close
 
DoCmd.Maximize
 
Exit_Continue:
    Set rs = Nothing
    Set qdf = Nothing
    Exit Sub
Error_Routine:
        MsgBox "Error " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
 

I tried your code and I get an Error: "Error 3265 Item not found in this collection"

[mlcampeau]

I tried your code and I get an Error: "Error 3265 Item not found in this collection"

Okay I made an adjustment to your code because it did not have the proper query name.

Set qdf = db.QueryDefs("MY - JobVacancyJobs")

(main report based on that query) which only has the fields Job Code, Job Title, Job Grade, Job Level, Job Group, and I got an error so I tried

Set qdf = db.QueryDefs("MY - JobVacancies")

(subreport based on that query) which has the fields JobCode, DepartmentCode, DivisionCode, NumberofPositio ns and I get the same error as the above code: Error 2427 You entered an expression that has no value.
Is this because TotalVacancies and NumVacancies are not actually in the query, but calculated on the report/subreport? When I click OK, it goes to my report, but doesn't filter anything.

[puppydogbuddy]

Okay I made an adjustment to your code because it did not have the proper query name.

Set qdf = db.QueryDefs("MY - JobVacancyJobs")

(main report based on that query) which only has the fields Job Code, Job Title, Job Grade, Job Level, Job Group, and I got an error so I tried

Set qdf = db.QueryDefs("MY - JobVacancies")

(subreport based on that query) which has the fields JobCode, DepartmentCode, DivisionCode, NumberofPositio ns and I get the same error as the above code: Error 2427 You entered an expression that has no value.
Is this because TotalVacancies and NumVacancies are not actually in the query, but calculated on the report/subreport? When I click OK, it goes to my report, but doesn't filter anything.

No, the recordset should reflect the effect of querydef and parameter. try eliminating parameter 2 (line 11) and see if main report reflects parameter 1.

[mlcampeau]

No, the recordset should reflect the effect of querydef and parameter. try eliminating parameter 2 (line 11) and see if main report reflects parameter 1.

Removing parameter 2 gives the same error: Error 2427 You entered an expression that has no value.
Removing parameter 1 and changing parameter 2 to parameter 1 gives Error 2455 You entered an expression that has an invalid reference to the property Form/Report

qdf.Parameters(1) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0

[puppydogbuddy]

My mistake, I meant for you to remove the parameter for the subreport not the one for the main report.

[mlcampeau]

My mistake, I meant for you to remove the parameter for the subreport not the one for the main report.

Sorry, I mustn't have been clear in that last post. I removed the parameter for the subreport and it gave me the Error 2427 You entered an expression that has no value.
When that didn't succeed, I tried removing the parameter for the main report instead, and that gave me the Error 2455 You entered an expression that has an invalid reference to the property Form/Report.

[mlcampeau]

Would it be any easier to do the calculation of TotalVacancies and NumberofVacanci es in VBA rather than referring to the textbox on the report? I'd probably still have the same problem of the queries not executing first, though, wouldn't I?

[puppydogbuddy]

Would it be any easier to do the calculation of TotalVacancies and NumberofVacanci es in VBA rather than referring to the textbox on the report? I'd probably still have the same problem of the queries not executing first, though, wouldn't I?

Yes. The easiest way would be to include it in each of your select statements. Strangely enough, per this link, you can include a textbox in your select statement...the link uses a textbox on a form as an example but I think it could be a textbox on a report also.

see comment by Duane Hookum
http://www.tek-tips.com/viewthread.c...361948&page=15

I am still researching this, but you are welcome to try the method outilined in the above link, including your report textbox in each of the select statement as illustrated by Hookum in the above link with the selection list inclusion of the textbox from a form. Be sure and include the full reference to your textboxes, e.g. Reports![JobVacanciesOnl y]![TotalVacancies].Value > 0......

[mlcampeau]

Yes. The easiest way would be to include it in each of your select statements. Strangely enough, per this link, you can include a textbox in your select statement...the link uses a textbox on a form as an example but I think it could be a textbox on a report also.

see comment by Duane Hookum
http://www.tek-tips.com/viewthread.c...361948&page=15

I am still researching this, but you are welcome to try the method outilined in the above link, including your report textbox in each of the select statement as illustrated by Hookum in the above link with the selection list inclusion of the textbox from a form. Be sure and include the full reference to your textboxes, e.g. Reports![JobVacanciesOnl y]![TotalVacancies].Value > 0......

Okay, now my question is this. I created my queries in the query design view, not in vba. Does that make a difference? Would I just add the textbox reference to my already existing query or should I have created the report based on a query written in vba?