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!

[mlcampeau]

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?

Changing my MY - JobVacancies query to:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
WHERE Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]>0
GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

and my MY - JobVacancyJobs query to:

SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc, Reports!JobVacanciesOnly![TotalVacancies] AS TotalVac
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
WHERE Reports!JobVacanciesOnly![TotalVacancies]>0
ORDER BY Job.Title, Job.Code;

doesn't cause any errors. The report opens, but doesn't display any info. All fields on the main report are blank except Title which shows #Error and nothing on the subreport displays.

[puppydogbuddy]

Whether you did it in design view on the query grid or used VBA should not have made any difference because Access will convert the grid to executable sql. I assume that your ReportOpen code just used the basic DoCmd.Open report syntax. Try commenting out all your ReportOpen code and open the report manually by clicking on the report itelf from the Access Report Window.

I will do some more research tomorrow. I know there are other alternatives, like creating NumVacancies and TotalVacancies as alias items in the applicable query instead of referencing to the textbox. To do this go to your query grid for each select statement, create an alias column as shown:
NumVacancies:DL ookup......blah ...blah
then like before, put > 0 in the criteria row. This method should definitely work because it should show the results in your query, when you run the query by itself.

[mlcampeau]

I assume that your ReportOpen code just used the basic DoCmd.Open report syntax. Try commenting out all your ReportOpen code and open the report manually by clicking on the report itelf from the Access Report Window.

I do not have any ReportOpen code. I open the report from the Access Report Window right now and do not have any forms or buttons to click in order to open it.

[puppydogbuddy]

I do not have any ReportOpen code. I open the report from the Access Report Window right now and do not have any forms or buttons to click in order to open it.

Ok, then. did you try the alias column in query method I suggested in the same post?

[mlcampeau]

I know there are other alternatives, like creating NumVacancies and TotalVacancies as alias items in the applicable query instead of referencing to the textbox. To do this go to your query grid for each select statement, create an alias column as shown:
NumVacancies:DL ookup......blah ...blah
then like before, put > 0 in the criteria row. This method should definitely work because it should show the results in your query, when you run the query by itself.

I can't seem to get the DLookup to work proper. I tried Dlookup, but then changed it to DCount (since I want the number to do the math) and I'm getting the same problem. I tried the following code in order to get the total number of occupants for each position in each division so that I can subtract it from the Number of Positions, in order to get the number of vacancies:
TotalOccupants: DCount("[MY - JobVacancyOccup ants]![ID]","MY - JobVacancyOccup ants")
That code gives me a number of 610 for every record in the query. (It's basically counting every employee once for each and every job title in the database) I then tried adding criteria in the code:
TotalOccupants: DCount("[MY - JobVacancyOccup ants]![ID]","MY - JobVacancyOccup ants","[MY - JobVacancyOccup ants]![JobCode]=[Job]![code]")
I get an error: The expression you entered as a query parameter produced this error: 'HRSource can't find the name 'Job!Code' you entered in the expression'.
I tried different syntax like [Job].[code], Code, Job.Code and they all give the same error. Same thing happens if I try to add criteria regarding the division.
This is starting to get sooo frustrating!! As smart as computers are, I wish they were a little bit smarter! Or maybe it's me that should be a little bit smarter....

[puppydogbuddy]

I can't seem to get the DLookup to work proper. I tried Dlookup, but then changed it to DCount (since I want the number to do the math) and I'm getting the same problem. I tried the following code in order to get the total number of occupants for each position in each division so that I can subtract it from the Number of Positions, in order to get the number of vacancies:
TotalOccupants: DCount("[MY - JobVacancyOccup ants]![ID]","MY - JobVacancyOccup ants")
That code gives me a number of 610 for every record in the query. (It's basically counting every employee once for each and every job title in the database) I then tried adding criteria in the code:
TotalOccupants: DCount("[MY - JobVacancyOccup ants]![ID]","MY - JobVacancyOccup ants","[MY - JobVacancyOccup ants]![JobCode]=[Job]![code]")
I get an error: The expression you entered as a query parameter produced this error: 'HRSource can't find the name 'Job!Code' you entered in the expression'.
I tried different syntax like [Job].[code], Code, Job.Code and they all give the same error. Same thing happens if I try to add criteria regarding the division.
This is starting to get sooo frustrating!! As smart as computers are, I wish they were a little bit smarter! Or maybe it's me that should be a little bit smarter....

LOL!! It can be frustrating at times, but if you have patience you will find the solution.

I am not sure why you need the second expression referenced above, because whatever level [ID] is on is the level that you will get the output. Nevertheless, I revised the second expression, assuming that Code is numeric, and also reivised assuming that [code] is a text data type.

By the way, it is not good practice to have spaces and hyphens in object names because Access will require references to these names to be enclosed with brackets. A better practice for creating Names should be like this:
MyJobVacancyOcc upants
or
My_JobVacancyOc cupants

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]")

Revised syntax for your second expression, assuming that [code] is numeric.
TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[code] = “ & [Job]![code])

Revised syntax for your second expression, assuming that [code] is text.
TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[code] = “ & [Job]![code] & “’”

[mlcampeau]

Thanks for the info on the names of my queries. I wasn't planning on keeping the names that way - I was just trying to keep track of the queries that I had added to this database so they were easier for me to locate so I started them all with my initials.
As for the code...

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]")

gives me 610 in every record (so 610 occupants for each and every job code in the database)
I tried changing that to
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[code] = “ & [Job]!

)&"'")

in order to try get the DCount to count the ID's where the Job Code in the MY - JobVacancyOccup ants query matches the job code in the Job table. I get a message box asking me to enter the parameter value for Job!Code.
The rest of my sql query looks like this:

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
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

*sigh* I just don't know! Oh, and Job Code is a text field

[puppydogbuddy]

Thanks for the info on the names of my queries. I wasn't planning on keeping the names that way - I was just trying to keep track of the queries that I had added to this database so they were easier for me to locate so I started them all with my initials.
As for the code...

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]")

gives me 610 in every record (so 610 occupants for each and every job code in the database)
I tried changing that to
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[code] = “ & [Job]!

)&"'")

in order to try get the DCount to count the ID's where the Job Code in the MY - JobVacancyOccup ants query matches the job code in the Job table. I get a message box asking me to enter the parameter value for Job!Code.
The rest of my sql query looks like this:

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
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

*sigh* I just don't know! Oh, and Job Code is a text field

We'll get there. Looked at your query and realized it is JobCode not Job!Code
so, try this:

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [JobVacancy]![JobCode] &"'")

[mlcampeau]

We'll get there. Looked at your query and realized it is JobCode not Job!Code
so, try this:

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [JobVacancy]![JobCode] &"'")

Okay, well, this just proved that I'm losing my mind. Completely ignore my last post because I was putting the information in the wrong query!! That query is what my subreport is based on, and I'm trying to work on the TotalOccupants based on the query of the main report. So.....When I try
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [Job]!

 &"'")

low and behold, it gives me the total occupants for that position!! Now I just need to figure out the correct expression to subtract that from JobVacancy.Numb erofPositions.
And, FYI, the query for the main report is now
[code=sql]SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategory Code, EEOCategory.Des c, Job.EEOSubCateg oryCode, EEOSubCategory. Desc, DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [Job]!

 & "'") AS TotalOccupants
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;

[mlcampeau]

[code=sql]SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategory Code, EEOCategory.Des c, Job.EEOSubCateg oryCode, EEOSubCategory. Desc, DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [Job]![code] & "'") AS TotalOccupants, DSum("[NumberofPositio ns]","JobVacancy", "[JobVacancy]![JobCode]='" & [Job]!

 & "'") AS TotalPositions, [TotalPositions]-[TotalOccupants] AS TotalVacancies
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;

You have NO idea how long I tried making a query that would give me the accurate numbers for TotalOccupants, TotalPositions, TotalVacancies! ! That's why I was calculating them on the report! But it works!!!! Now I need to try doing that with the query for the subform (it'll probably be a bit more difficult cuz the divisions need to match) But thank you thank you thank you for getting me this far!!