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 am still reading and digesting your last post, but I would like to resolve the following issue:

<<<<. I'm still having problems with the DCount referencing [Job]![code]>>>>

Now, I am confused....I thought this query involved these 2 tables:
1. [My - JobVacancyOccup ants]
2. [JobVacancy]

When did table [Job] come into this query? I know you have it the main report query, but I don't believe you had it in the version of this query that you presented on friday.....so therefore I believe that [Job]![code] should be [JobVacancy]![JobCode]

[mlcampeau]

I am still reading and digesting your last post, but I would like to resolve the following issue:

<<<<. I'm still having problems with the DCount referencing [Job]![code]>>>>

Now, I am confused....I thought this query involved these 2 tables:
1. [My - JobVacancyOccup ants]
2. [JobVacancy]

When did table [Job] come into this query? I know you have it the main report query, but I don't believe you had it in the version of this query that you presented on friday.....so therefore I believe that [Job]![code] should be [JobVacancy]![JobCode]

Oh dear...I had to have a little chuckle after reading your post. It seems we are just both confusing each other now! I have always been referencing [Job]![code] (even if you check back to the posts from Aug. 9 it shows that.)
The query we're trying to make work right now indeed involves the table JobVacancy and another query MY - JobVacancyOccup ants. The reason I am wanting to reference [Job]![code] is because not all job codes are entered in the JobVacancy table and not all job codes are assigned to an employee, so it wouldn't be in the MY - JobVacancyOccup ants query results. Therefore, I am wanting to have all job codes from the job table displaying in the end result of this query, as well as all departments and divisions associated with that code (whether it be from the job vacancy table or MY - JobVacancyOccup ants), as well as the Number of positions, and number of occupants (Our DCount expression). Once I get that to work, then I can calculate NumberofPositio ns-NumOccupants to find out the number of vacancies. If you recall (I know, it's been a long time since we've actually talked about the whole point of this query) I have a calculated textbox on a report that I am wanting to base a filter upon but had no luck, so we were trying to add that calculation into my query so that I can filter out those job codes that had the NumberofVacanci es > 0.
Maybe I've been going about this all wrong, I don't know.

[puppydogbuddy]

At least one problem will be cleared up.....The reason Job!Code is not working for you now is because you did not include it on the selection list. In order to do so, you must include table [Job] in the query grid. So your query grid will now have 3 tables that are joined in some fashion.

[puppydogbuddy]

However, if you just want to reference it in the DCount portion without putting it on the selection list, then you don't need it on the selection list, but you must reference the [Job] table (not MyVacancyOccupa nts) as the source for [ID] in the Dcount. .

[mlcampeau]

However, if you just want to reference it in the DCount portion without putting it on the selection list, then you don't need it on the selection list, but you must reference the [Job] table (not MyVacancyOccupa nts) as the source for [ID] in the Dcount. .

That won't work because the Job table only has the following fields:
Code
EEOCategoryCode
EEOSubCategoryC ode
Grade
Title
and a couple other randoms that are associated with that particular code.

I just tried making a query referencing the JobVacancy table and the job table:

SELECT job.Code, JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions
FROM job LEFT JOIN JobVacancy ON job.Code = JobVacancy.JobCode
ORDER BY job.Code;

and then did the same with the MY - JobVacancyOccup ants and job table:

SELECT Job.Code, [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].ID, [MY - JobVacancyOccupants].DepartmentCode, [MY - JobVacancyOccupants].DivisionCode, [MY - JobVacancyOccupants].StatusCode
FROM [MY - JobVacancyOccupants] RIGHT JOIN Job ON [MY - JobVacancyOccupants].JobCode = Job.Code
WHERE (((Job.Code) Is Not Null));

and then tried making a query based off of that, since those two queries produce records that include every job code in the job table:
[code=sql]SELECT MY_JobVacancyOc cupants2.Code, MY_JobVacAndJob Table.Departmen Code, MY_JobVacAndJob Table.DivisionC ode, MY_JobVacancyOc cupants2.Depart mentCode, MY_JobVacancyOc cupants2.Divisi onCode, MY_JobVacAndJob Table.NumberofP ositions, DCount("[ID]","[MY_JobVacancyOc cupants2]","[code] ='" & [MY_JobVacAndJob Table]!

 & "' AND [MY_JobVacancyOccupants2]![DivisionCode]='" & [MY_JobVacAndJobTable]![DivisionCode] & "'") AS NumOccupants
FROM MY_JobVacancyOccupants2 INNER JOIN MY_JobVacAndJobTable ON (MY_JobVacancyOccupants2.DivisionCode = MY_JobVacAndJobTable.DivisionCode) AND (MY_JobVacancyOccupants2.DepartmentCode = MY_JobVacAndJobTable.DepartmenCode) AND (MY_JobVacancyOccupants2.Code = MY_JobVacAndJobTable.Code);

The problem is joining those two queries. I have the option of right join, left join, or inner join. The problem is that I don't want any of the information in either of those two queries excluded. The inner join leaves out those records that don't match. The left join leaves out records from MY_JobVacAndJob Table and a right join leaves out records from MY_JobVacancyOc cupants2. Is there any way to include all the records?

[puppydogbuddy]

That won't work because the Job table only has the following fields:
Code
EEOCategoryCode
EEOSubCategoryC ode
Grade
Title
and a couple other randoms that are associated with that particular code.

I just tried making a query referencing the JobVacancy table and the job table:

SELECT job.Code, JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions
FROM job LEFT JOIN JobVacancy ON job.Code = JobVacancy.JobCode
ORDER BY job.Code;

and then did the same with the MY - JobVacancyOccup ants and job table:

SELECT Job.Code, [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].ID, [MY - JobVacancyOccupants].DepartmentCode, [MY - JobVacancyOccupants].DivisionCode, [MY - JobVacancyOccupants].StatusCode
FROM [MY - JobVacancyOccupants] RIGHT JOIN Job ON [MY - JobVacancyOccupants].JobCode = Job.Code
WHERE (((Job.Code) Is Not Null));

and then tried making a query based off of that, since those two queries produce records that include every job code in the job table:
[code=sql]SELECT MY_JobVacancyOc cupants2.Code, MY_JobVacAndJob Table.Departmen Code, MY_JobVacAndJob Table.DivisionC ode, MY_JobVacancyOc cupants2.Depart mentCode, MY_JobVacancyOc cupants2.Divisi onCode, MY_JobVacAndJob Table.NumberofP ositions, DCount("[ID]","[MY_JobVacancyOc cupants2]","[code] ='" & [MY_JobVacAndJob Table]!

 & "' AND [MY_JobVacancyOccupants2]![DivisionCode]='" & [MY_JobVacAndJobTable]![DivisionCode] & "'") AS NumOccupants
FROM MY_JobVacancyOccupants2 INNER JOIN MY_JobVacAndJobTable ON (MY_JobVacancyOccupants2.DivisionCode = MY_JobVacAndJobTable.DivisionCode) AND (MY_JobVacancyOccupants2.DepartmentCode = MY_JobVacAndJobTable.DepartmenCode) AND (MY_JobVacancyOccupants2.Code = MY_JobVacAndJobTable.Code);

The problem is joining those two queries. I have the option of right join, left join, or inner join. The problem is that I don't want any of the information in either of those two queries excluded. The inner join leaves out those records that don't match. The left join leaves out records from MY_JobVacAndJob Table and a right join leaves out records from MY_JobVacancyOc cupants2. Is there any way to include all the records?

Yes......use a union query. If you don't know how to do a union query, I will look at it tomorrow.

[mlcampeau]

Yes......use a union query. If you don't know how to do a union query, I will look at it tomorrow.

I've never used a union query before...it's getting to be the end of the day for me, but I will try to play around with it tomorrow, as well, to see what I can come up with.

[mlcampeau]

It's been a while but I'm revisiting this issue now. I have got my queries working. My main report query is the same as before and my subreport query is now:
[code=sql]SELECT Job.Code, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions, Department.Desc , Division.Desc, IIf((DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]![code] & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'"))<0 Or (DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]![code] & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'")) Is Null,0,(DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]!

 & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'"))) AS TotalPositionsFilled, iif([JobVacancy.NumberofPositions]-[TotalPositionsFilled]<0,0, [JobVacancy.NumberofPositions]-[TotalPositionsFilled]) AS Vacancies
FROM Job LEFT JOIN ((JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code) ON Job.Code = JobVacancy.JobCode
ORDER BY Job.Code, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

As mentioned in a way earlier post, I have been able to filter the report to show me when the TotalVacancies on the main report is greater than 1. So a sample output would be:

Divisional Accountant: Total Positions 5, Total Occupants 4, Total Vacancies 1

And the subreport would break down the information into:

Department: BS Division TIMBS Total Positions 2, Total Occupants 2, Total Vacancies 0
Department: BS Division: FSS Total Positions 2, Total Occupants 1, Total Vacancies 1

Now, instead of showing both the TIMBS and FSS divisions, I would only want the FSS division to display since it's the only one with a vacancy.
Currently I have this code:

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

which works great for filtering out the main report. Now, as I said, I would like to filter out the subreport as well. In all my previous postings I was trying to filter based on a calculated textbox on the report. I was finally able to do the calculation in the query so now I have a control named Vacancies on the subreport. In the end, I want the subreport to only display records where Vacancies>1. As I said, I was able to filter the main report just fine, but am now not sure exactly how to incorporate the subreport information in this. Any help would be appreciated!! Thanks!

[puppydogbuddy]

It's been a while but I'm revisiting this issue now. I have got my queries working. My main report query is the same as before and my subreport query is now:
[code=sql]SELECT Job.Code, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions, Department.Desc , Division.Desc, IIf((DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]![code] & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'"))<0 Or (DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]![code] & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'")) Is Null,0,(DSum("[FTE]","[MY_JobCodeFTE]","[JobCode] = '" & [Job]!

 & "' AND [DivisionCode]= '" & [JobVacancy].[DivisionCode] & "'"))) AS TotalPositionsFilled, iif([JobVacancy.NumberofPositions]-[TotalPositionsFilled]<0,0, [JobVacancy.NumberofPositions]-[TotalPositionsFilled]) AS Vacancies
FROM Job LEFT JOIN ((JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code) ON Job.Code = JobVacancy.JobCode
ORDER BY Job.Code, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

As mentioned in a way earlier post, I have been able to filter the report to show me when the TotalVacancies on the main report is greater than 1. So a sample output would be:

Divisional Accountant: Total Positions 5, Total Occupants 4, Total Vacancies 1

And the subreport would break down the information into:

Department: BS Division TIMBS Total Positions 2, Total Occupants 2, Total Vacancies 0
Department: BS Division: FSS Total Positions 2, Total Occupants 1, Total Vacancies 1

Now, instead of showing both the TIMBS and FSS divisions, I would only want the FSS division to display since it's the only one with a vacancy.
Currently I have this code:

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

which works great for filtering out the main report. Now, as I said, I would like to filter out the subreport as well. In all my previous postings I was trying to filter based on a calculated textbox on the report. I was finally able to do the calculation in the query so now I have a control named Vacancies on the subreport. In the end, I want the subreport to only display records where Vacancies>1. As I said, I was able to filter the main report just fine, but am now not sure exactly how to incorporate the subreport information in this. Any help would be appreciated!! Thanks!

Hi MLCampeau,

I wondered what happened! In regards to your unresolved problem with the subreport filter:
1. Since you are using 2 completly different queries for your main report and subreport, I would first try using a filter statement in the open or activate event of the subreport as follows and see what happens:

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

_______________ _______________ _____________

Alternatively, if you want to try calling the subreport filter from the main report, you could try this:

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

[mlcampeau]

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.