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]

Private Sub Report_Open(Cancel As Integer)     'the main report
Dim strWhere As String
 
strWhere = "[TotalVacancies]>0"
Me!JobVacanciesOnlySR.Report.Filter = "DivisionCode = FSS"
Me!JobVacanciesOnlySR.Report.FilterOn = True 
 
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
End Sub

With the above code, I get Run-time error 2455; You entered an expression that has an invalid reference to the property Form/Report. I ran the debug and I got the error at this line:
Me!JobVacancies OnlySR.Report.F ilter = "DivisionCo de = FSS"
Is the syntax incorrect??

Private Sub Report_Open          'this is the subreport's open event
 
Dim strFilter As String
 
strFilter =  "DivisionCode = FSS"      
Me.Filter =  strFilter
Me.FilterOn = True
 
End Sub

With that code, the report runs, but does not filter. I should just clarify that I don't want my filter to show DivisionCode=FS S. It was just in the example that I provided that that's how it should've filtered. I want the filter to be Vacancies>0. I tried replacing it and it doesn't make a difference with the problems that came up.

I will check this out later on in the day and get back to you. In the meantime, try moving the subreport filter from the subreport open event to the subreport activate event (as shown) and rerun it.

Private Sub Report_Activate()          'this is the subreport's activate event
 
Dim strFilter As String
 
strFilter =  "DivisionCode = FSS"      
Me.Filter =  strFilter
Me.FilterOn = True
 
End Sub

[mlcampeau]

I tried moving the code to the On Activate event and the report runs, but does not filter. I also tried putting single quotes around 'FSS' and that didn't make a difference.

[puppydogbuddy]

I tried moving the code to the On Activate event and the report runs, but does not filter. I also tried putting single quotes around 'FSS' and that didn't make a difference.

Try changing this:
strFilter = "DivisionCo de = FSS"

To:
strFilter = "DivisionCo de = '" & 'FSS' & "'"

[mlcampeau]

Oh puppydogbuddy, I feel so dumb. I was trying to filter the subreport through the report because I had two reports based on the same query. (One report showing only vacancies, while the other showed all jobs and vacancies whether it was 0 or not). Well, I went and duplicated the query and filtered the query so that it only shows Vacancies>0, and voila! My report is completely filtered. I knew it had to be something simple!! Thanks for all your efforts!!!! It's been greatly appreciated!

[Jim Doherty]

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!

Oh myyyyy this thread must be the longest in the history of the world LOL (says he as he ducks his head down, burns out his mousewheel, reads little and dives out the room) :)

Jim