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]

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.

Okay so I think I have the Is Null part working.
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR (([MY - JobVacancyOccup ants]![DivisionCode] Is Null) And [JobCode] = '" & [Job]![code] & "') OR ('" & [JobVacancy]![DivisionCode] & "' IS Null) AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants

What happens now is probably better explained with an example.
This record comes up when I run the query:
Job Code; DepartmentCode; DivisionCode; OccDept; OccDiv; #Positions; #Occupants
30801A; ES; HR; ES; HR; 1; 2

Where DepartmentCode and DivisionCode are from the JobVacancy table while OccDept and OccDiv are from the EmplOrganizatio n table.
This is correct in the fact that there are 2 occupants with this job code, however, 1 of the occupants is indeed in Department ES, Division HR, while the other is in Department: OP, Division: Null. Soooo....In fact I should have two separate records showing like this:
30801A; ES; HR; ES; HR; 1; 1
30801A; Null; Null; Op; Null; Null; 1
(Nulls because the position with the department Op and Division Null are not in the JobVacancy table, and Division is Null because it's not entered in the system for that individual)
Now, maybe seeing that example you'll have a better idea of what I'm trying to accomplish (I hope. It's so hard to explain...) If you have any ideas on how I can get the desired results, I'm listening!

[puppydogbuddy]

Okay so I think I have the Is Null part working.
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR (([MY - JobVacancyOccup ants]![DivisionCode] Is Null) And [JobCode] = '" & [Job]![code] & "') OR ('" & [JobVacancy]![DivisionCode] & "' IS Null) AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants

What happens now is probably better explained with an example.
This record comes up when I run the query:
Job Code; DepartmentCode; DivisionCode; OccDept; OccDiv; #Positions; #Occupants
30801A; ES; HR; ES; HR; 1; 2

Where DepartmentCode and DivisionCode are from the JobVacancy table while OccDept and OccDiv are from the EmplOrganizatio n table.
This is correct in the fact that there are 2 occupants with this job code, however, 1 of the occupants is indeed in Department ES, Division HR, while the other is in Department: OP, Division: Null. Soooo....In fact I should have two separate records showing like this:
30801A; ES; HR; ES; HR; 1; 1
30801A; Null; Null; Op; Null; Null; 1
(Nulls because the position with the department Op and Division Null are not in the JobVacancy table, and Division is Null because it's not entered in the system for that individual)
Now, maybe seeing that example you'll have a better idea of what I'm trying to accomplish (I hope. It's so hard to explain...) If you have any ideas on how I can get the desired results, I'm listening!

Instead of Is null, try the Null To Zero function and see if that helps and let me know....I have other ideas if that does not work.

[mlcampeau]

Instead of Is null, try the Null To Zero function and see if that helps and let me know....I have other ideas if that does not work.

This is my full sql:
[code=sql]SELECT Job.Code, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions, Department.Desc , Division.Desc,
DCount("[ID]","[MY - JobVacancyOccup ants]","( [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccup ants]![DivisionCode]) And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "')") AS NumOccupants, (DLookUp("[DepartmentCode]","MY - JobVacancyOccup ants","[JobCode]='" & [Job]![code] & "'")) AS OccDept, DLookUp("[DivisionCode]","MY - JobVacancyOccup ants","[JobCode]='" & [Job]!

 & "'") AS OccDiv
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, IIf([JobVacancy].[DepartmenCode] Is Null,"z-Unassigned",[JobVacancy].[DepartmenCode]), IIf([JobVacancy].[DivisionCode] Is Null,"z-Unassigned",[JobVacancy].[DivisionCode]);

where
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","( [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccup ants]![DivisionCode]) And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]!

 & "')") AS NumOccupants

is the piece of code that I've been focusing on. (who knows, maybe the answer lies in the DLookups?) As you can see, I tried the Null to Zero function and I have the same results as the Is Null in regards to my example in the previous post. What I did not realize with the Is Null until a few moments ago is that when I had a case where an employee had a particular job but the job was not in the job vacancy table, the NumOccupants was coming up as 0, but the Nz gives the correct count in those cases. With the above code, it seems that the trouble is lying where individuals do not have a department and/or division assigned to them.

[mlcampeau]

This is my full sql:
[code=sql]SELECT Job.Code, JobVacancy.Depa rtmenCode, JobVacancy.Divi sionCode, JobVacancy.Numb erofPositions, Department.Desc , Division.Desc,
DCount("[ID]","[MY - JobVacancyOccup ants]","( [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccup ants]![DivisionCode]) And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "')") AS NumOccupants, (DLookUp("[DepartmentCode]","MY - JobVacancyOccup ants","[JobCode]='" & [Job]![code] & "'")) AS OccDept, DLookUp("[DivisionCode]","MY - JobVacancyOccup ants","[JobCode]='" & [Job]!

 & "'") AS OccDiv
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, IIf([JobVacancy].[DepartmenCode] Is Null,"z-Unassigned",[JobVacancy].[DepartmenCode]), IIf([JobVacancy].[DivisionCode] Is Null,"z-Unassigned",[JobVacancy].[DivisionCode]);

where
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","( [MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccup ants]![DivisionCode]) And [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccup ants]![JobCode] = '" & [Job]!

 & "')") AS NumOccupants

is the piece of code that I've been focusing on. (who knows, maybe the answer lies in the DLookups?) As you can see, I tried the Null to Zero function and I have the same results as the Is Null in regards to my example in the previous post. What I did not realize with the Is Null until a few moments ago is that when I had a case where an employee had a particular job but the job was not in the job vacancy table, the NumOccupants was coming up as 0, but the Nz gives the correct count in those cases. With the above code, it seems that the trouble is lying where individuals do not have a department and/or division assigned to them.

I just noticed a flaw with the Nz(). Again, I'll illustrate it as an example. This is what I want:
JobCode; DepartmentCode; DivisionCode; OccDept;OccDiv; #Pos;#Occ
30400A; TIMB; CRR; TIMB; CRR; 1; 1
30400A; TIMB; NVIR; TIMB; NVIR; 1; 1
30400A; TIMB; WIR; TIMB; WIR; 1; 1
but when I changed the code to Nz() instead of Is Null, I got this:
30400A; TIMB; CRR; TIMB; NVIR; 1; 3
30400A; TIMB; NVIR; TIMB; NVIR; 1; 3
30400A; TIMB; WIR; TIMB; NVIR; 1; 3
So it is counting all 3 occupants each time rather than matching the divisions and counting that way.

[mlcampeau]

Just incorporate your filter condition in the Where argument of the OpenReport command like the following:

Dim strWhere As String
strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
DoCmd.OpenRepor t "YourReportName ", acViewPreview, , strWhere

Well, I have a little bit of good news from all of this. Regarding the query MY - JobVacancyJobs (the one the main report is based off of. aka-the one that works), I took out the calculated textbox TotalVacancies from my main report and replaced it with the TotalVacancies control from the query. I then put this code in:

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

And it filters the report!! I would still like to get the second query working properly because in cases where a JobCode is associated with many departments and divisions, but only 1 of them have a vacancy, I only want that one to show up in the subreport, where right now, it's showing all the departments and divisions, but it is only showing the JobCodes that have vacancies! It does really slow down the opening of the report but it works!

[puppydogbuddy]

Here are a couple of things for you to think about: The easiest one to implement is #2, so you might want to try that first. Let me know what happens.

1. many errors are made with the improper use of "Ands" and "Ors". Make sure what you are wanting to do matches the syntax that you used. Map it out in simplistic terms what you want to do and then match it to the syntax used in the expression.

x+ y And (A or B) And (C or D)

X + Y And (A or B or C)

X + Y OR (A and B and C)
_______________ _______________ _______________ ___________
2. Try your second query using the Distinct key word.>>>>Select Distinct blah blah

_______________ _______________ _______________ _______________ _____
3. If nulls are not counted improperly on NumOccupants, keep in mind that you you could use the null to zero string function, which converts the null to text..usually spaces ("") , example Nz([XXXX],"")

[mlcampeau]

Here are a couple of things for you to think about: The easiest one to implement is #2, so you might want to try that first. Let me know what happens.

1. many errors are made with the improper use of "Ands" and "Ors". Make sure what you are wanting to do matches the syntax that you used. Map it out in simplistic terms what you want to do and then match it to the syntax used in the expression.

x+ y And (A or B) And (C or D)

X + Y And (A or B or C)

X + Y OR (A and B and C)
_______________ _______________ _______________ ___________
2. Try your second query using the Distinct key word.>>>>Select Distinct blah blah

_______________ _______________ _______________ _______________ _____
3. If nulls are not counted improperly on NumOccupants, keep in mind that you you could use the null to zero string function, which converts the null to text..usually spaces ("") , example Nz([XXXX],"")

I tried using Distinct and that doesn't change anything.
I'm not sure if I'm understanding your #3 correctly. I tried this:
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccup ants]![DivisionCode]," ")) And [JobCode] = '" & [Job]![code] & "') OR ((Nz('" & [JobVacancy]![DivisionCode] & "', " ")) AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants

but it doesn't like the quotation marks in the Nz() function

[puppydogbuddy]

I tried using Distinct and that doesn't change anything.
I'm not sure if I'm understanding your #3 correctly. I tried this:
[code]DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccup ants]![DivisionCode]," ")) And [JobCode] = '" & [Job]![code] & "') OR ((Nz('" & [JobVacancy]![DivisionCode] & "', " ")) AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants

but it doesn't like the quotation marks in the Nz() function

Wrong syntax...ths should be pretty close:
[code]
DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccup ants]![DivisionCode],"")) And [JobCode] = '" & [Job]![code] & "') OR '" & ((Nz([JobVacancy]![DivisionCode],"") & "') AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants
 

[mlcampeau]

Wrong syntax...ths should be pretty close:
[code]
DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccup ants]![DivisionCode],"")) And [JobCode] = '" & [Job]![code] & "') OR '" & ((Nz([JobVacancy]![DivisionCode],"") & "') AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants
 

That gives me a syntax error (missing operator) and then it highlights AS.

[puppydogbuddy]

That gives me a syntax error (missing operator) and then it highlights AS.

Try this:
[code]
DCount("[ID]","[MY - JobVacancyOccup ants]","([MY - JobVacancyOccup ants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccup ants]![DivisionCode],"") & "') And [JobCode] = '" & [Job]![code] & "') OR '" & ((Nz([JobVacancy]![DivisionCode],"") & "') AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants