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]

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

Told you you'd get there. Try the subreport computation and let me know one way or another. I will help you with the syntax if you have any trouble. You are on a roll now!

[mlcampeau]

Told you you'd get there. Try the subreport computation and let me know one way or another. I will help you with the syntax if you have any trouble. You are on a roll now!

Okay, the quotes are really screwing me up.

DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] "' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants

I know they aren't right, but I don't understand them, so if you could help me with where the quotes go then I can try to see if this works. Thanks!!

[mlcampeau]

Okay, the quotes are really screwing me up.

DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] "' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants

I know they aren't right, but I don't understand them, so if you could help me with where the quotes go then I can try to see if this works. Thanks!!

So I came up with this:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants
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;

The problem is the same problem that I had a couple weeks ago when I tried to have a query incorporate the number of occupants. Right now, not every employee has a division entered, and not every job is in the job vacancy table (Yes, life would be easier if it were, but unfortunately, that is the way it is for now) Therefore, by saying [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] it filters out records that I need. It filters out job codes where employees don't have a division code, and it filters out job codes where the job is not in the Job Vacancy table. Any ideas on how to get it to show all?

[puppydogbuddy]

So I came up with this:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants
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;

The problem is the same problem that I had a couple weeks ago when I tried to have a query incorporate the number of occupants. Right now, not every employee has a division entered, and not every job is in the job vacancy table (Yes, life would be easier if it were, but unfortunately, that is the way it is for now) Therefore, by saying [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] it filters out records that I need. It filters out job codes where employees don't have a division code, and it filters out job codes where the job is not in the Job Vacancy table. Any ideas on how to get it to show all?

Ok, Try this:

DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants

As to your second question, it looks to me like you have it correct. Generally a left or right Join is used to pick up all records from one of the tables and only matching records from the other..If the left join is not giving it to you, turn your join into a right join and see if that helps ...

Let me know..

[mlcampeau]

Ok, Try this:

DCount("[ID]","[MY - JobVacancyOccup ants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants

As to your second question, it looks to me like you have it correct. Generally a left or right Join is used to pick up all records from one of the tables and only matching records from the other..If the left join is not giving it to you, turn your join into a right join and see if that helps ...

Let me know..

Yes, your code for DCount works (it's actually the same as what I had in my sql query in the previous post, but thanks!) As for the joins, that is definitely not the problem. I only show three tables in design view
Job Vacancy:
JobCode
DepartmenCode
DivisionCode
NumberofVacanci es
Department:
DepartmentCode
Department Description
Division]:
DivisionCode
DivisionDescrip tion

So the joins are just there to be able to get the descriptions of the department and division. Doesn't work if I change it to the other way. What I need the dcount to do is to count the people where JobVacancy.Divi sionCode=
[MY - JobVacancyOccup ants]![DivisionCode] OR [JobVacancy.Divi sionCode] IS NULL OR [MY - JobVacancyOccup ants]![DivisionCode] IS NULL. I hope that would give me the results I'm looking for. I'm just not 100% sure what the syntax would be. I tried

 DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND (Is Null([MY - JobVacancyOccupants]![DivisionCode]) OR Is Null([JobVacancy]![DivisionCode]) OR [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "')") AS NumOccupants

but then it displays #Error in that column........

[puppydogbuddy]

is division code numeric or text?

[mlcampeau]

is division code numeric or text?

DivisionCode is a text field

[puppydogbuddy]

Yes, your code for DCount works (it's actually the same as what I had in my sql query in the previous post, but thanks!) As for the joins, that is definitely not the problem. I only show three tables in design view
Job Vacancy:
JobCode
DepartmenCode
DivisionCode
NumberofVacanci es
Department:
DepartmentCode
Department Description
Division]:
DivisionCode
DivisionDescrip tion

So the joins are just there to be able to get the descriptions of the department and division. Doesn't work if I change it to the other way. What I need the dcount to do is to count the people where JobVacancy.Divi sionCode=
[MY - JobVacancyOccup ants]![DivisionCode] OR [JobVacancy.Divi sionCode] IS NULL OR [MY - JobVacancyOccup ants]![DivisionCode] IS NULL. I hope that would give me the results I'm looking for. I'm just not 100% sure what the syntax would be. I tried

 DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND (Is Null([MY - JobVacancyOccupants]![DivisionCode]) OR Is Null([JobVacancy]![DivisionCode]) OR [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "')") AS NumOccupants

but then it displays #Error in that column........

Try this and let me know. Your main problem was with Is Null. When used as a function, the syntax is "IsNull()"; when used as a criteria, the syntax is "Is Null"

DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND (IsNull([MY - JobVacancyOccupants]![DivisionCode]) OR IsNull([JobVacancy]![DivisionCode]) OR [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "')") AS NumOccupants

[mlcampeau]

Try this and let me know. Your main problem was with Is Null. When used as a function, the syntax is "IsNull()"; when used as a criteria, the syntax is "Is Null"

DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND (IsNull([MY - JobVacancyOccupants]![DivisionCode]) OR IsNull([JobVacancy]![DivisionCode]) OR [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "')") AS NumOccupants

Yeah, I caught that I had a space in there but it was still providing errors after I fixed it. I'll try the rest of your syntax in the morning, since it's time to head home!
ps - I think I'm going for the record for the longest thread!

[puppydogbuddy]

Longest thread.......no t even close!! IsNull may not do what you want ...if it doesn't, then use the IS Null criteria syntax.....

([MY - JobVacancyOccup ants]![DivisionCode] Is Null) OR ........blah blah.