Filter report based on values of calculated value on report

[mlcampeau]

I have a report (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) 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("[NumberofPositions]","JobVacancy", "[JobVacancy.JobCode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =[code]"))<0,0,((DSum("[NumberofPositions]","JobVacancy", "[JobVacancy.JobCode]=[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=IIf([NumberofPositions]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnly]![code] AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] "))<0,0,[NumberofPositions]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnly]!

 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.......not even close!! IsNull may not do what you want ...if it doesn't, then use the IS Null criteria syntax.....

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

Okay so I think I have the Is Null part working.
[code]DCount("[ID]","[MY - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR (([MY - JobVacancyOccupants]![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 EmplOrganization 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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR (([MY - JobVacancyOccupants]![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 EmplOrganization 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.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc,
DCount("[ID]","[MY - JobVacancyOccupants]","( [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccupants]![DivisionCode]) And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "')") AS NumOccupants, (DLookUp("[DepartmentCode]","MY - JobVacancyOccupants","[JobCode]='" & [Job]![code] & "'")) AS OccDept, DLookUp("[DivisionCode]","MY - JobVacancyOccupants","[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 - JobVacancyOccupants]","( [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccupants]![DivisionCode]) And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccupants]![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.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc,
DCount("[ID]","[MY - JobVacancyOccupants]","( [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccupants]![DivisionCode]) And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "')") AS NumOccupants, (DLookUp("[DepartmentCode]","MY - JobVacancyOccupants","[JobCode]='" & [Job]![code] & "'")) AS OccDept, DLookUp("[DivisionCode]","MY - JobVacancyOccupants","[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 - JobVacancyOccupants]","( [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz([MY - JobVacancyOccupants]![DivisionCode]) And [MY - JobVacancyOccupants]![JobCode] = '" & [Job]![code] & "') OR (Nz('" & [JobVacancy]![DepartmenCode] & "') AND [MY - JobVacancyOccupants]![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.OpenReport "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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccupants]![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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccupants]![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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccupants]![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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccupants]![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 - JobVacancyOccupants]","([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR ((Nz([MY - JobVacancyOccupants]![DivisionCode],"") & "') And [JobCode] = '" & [Job]![code] & "') OR '" & ((Nz([JobVacancy]![DivisionCode],"") & "') AND [JobCode] = '" & [Job]!

 & "'") AS NumOccupants

[mlcampeau]

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

 & "'") AS NumOccupants

Nope...same error but it highlights
((Nz([MY - JobVacancyOccupants]![DivisionCode],"") & "') And [JobCode] = '" &

[puppydogbuddy]

Nope...same error but it highlights
((Nz([MY - JobVacancyOccupants]![DivisionCode],"") & "') And [JobCode] = '" &

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

 & "'") AS NumOccupants

[mlcampeau]

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

 & "'") AS NumOccupants

Same problem I stated in the above post :(

[puppydogbuddy]

Same problem I stated in the above post :(

Ok, try this .....

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')")).
 

[puppydogbuddy]

oops! left out alias at the end.

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')" As NumOccupants))
 

[mlcampeau]

oops! left out alias at the end.

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')" As NumOccupants))
 

Sorry it took so long for a reply. It was a long weekend in Canada. I tried: [code]DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')")) As NumOccupants

and it gives me 609 for every job code.

[puppydogbuddy]

MLCampeau,
Congrats on becoming part of the expert team.

Try these three variations and tell me what happens (this is an experiment to observe the effects using 0 instead of "" for the nz, and the effects of running component parts of the full expression: The second and third expressions use "" in the nz function. If the output is not good with "", then try using 0 instead of "" for the nz.

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & “’ And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],0) & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], 0) & "' AND [JobCode] = '" & [Job]!

 & "')")) As NumOccupants
 

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & “’ And [JobCode] = '" & [Job]!

 & "')) As NumOccupants
 

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "(Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')")) As NumOccupants
 

[mlcampeau]

MLCampeau,
Congrats on becoming part of the expert team.

Try these three variations and tell me what happens (this is an experiment to observe the effects using 0 instead of "" for the nz, and the effects of running component parts of the full expression: The second and third expressions use "" in the nz function. If the output is not good with "", then try using 0 instead of "" for the nz.

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & “’ And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],0) & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], 0) & "' AND [JobCode] = '" & [Job]!

 & "')")) As NumOccupants
 

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & “’ And [JobCode] = '" & [Job]!

 & "')) As NumOccupants
 

[code]
DCount("[ID]", "[MY - JobVacancyOccupants]", "(Nz([MY - JobVacancyOccupants]![DivisionCode],"") & '" And [JobCode] = "' & [Job]![code] & '" Or (Nz([JobVacancy]![DivisionCode], "") & "' AND [JobCode] = '" & [Job]!

 & "')")) As NumOccupants
 

Thanks! I don't feel like much of an expert though since this query is giving me so much trouble!
The first query had a syntax error. I changed it to
[code]DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],0) & '" And [JobCode] = "' & [Job]![code] & '" Or Nz([JobVacancy]![DivisionCode], 0) & "' AND [JobCode] = '" & [Job]!

 & "')") As NumOccupants

and that gives me 609 for every record. I didn't see any zeros show up anywhere.
The second bit of code somewhat works. It gives me correct numbers when [MY - JobVacancyOccupants]![DivisionCode]=[JobVacancy]![DivisionCode] (imagine that!) BUT, in cases where an occupant holds a position in a division that is not in the job vacancy table, they are not counted.
i.e. I have one occupant with job code 30111A, in Department BS, Division CORP, but my count shows 0 because I do not have a matching job code/department/division in the job vacancy table. It at least shows the record.
Another example:
I have 2 people with job code 30801A. One of them is in Department ES, Division HR, the other is in Department OP, and division is null. This second bit of code only counts the first individual because I have job code 30801A, Department ES, Division HR in the job vacancy table, but not the other. It doesn't even show a record for Job Code 30801A, Department OP.
Your third bit of code gives 609 for every record again.

[puppydogbuddy]

Thanks! I don't feel like much of an expert though since this query is giving me so much trouble!
The first query had a syntax error. I changed it to
[code]DCount("[ID]", "[MY - JobVacancyOccupants]", "([MY - JobVacancyOccupants]![DivisionCode]= '" & [JobVacancy]![DivisionCode] & "' And [JobCode] = '" & [Job]![code] & "') OR Nz([MY - JobVacancyOccupants]![DivisionCode],0) & '" And [JobCode] = "' & [Job]![code] & '" Or Nz([JobVacancy]![DivisionCode], 0) & "' AND [JobCode] = '" & [Job]!

 & "')") As NumOccupants

and that gives me 609 for every record. I didn't see any zeros show up anywhere.
The second bit of code somewhat works. It gives me correct numbers when [MY - JobVacancyOccupants]![DivisionCode]=[JobVacancy]![DivisionCode] (imagine that!) BUT, in cases where an occupant holds a position in a division that is not in the job vacancy table, they are not counted.
i.e. I have one occupant with job code 30111A, in Department BS, Division CORP, but my count shows 0 because I do not have a matching job code/department/division in the job vacancy table. It at least shows the record.
Another example:
I have 2 people with job code 30801A. One of them is in Department ES, Division HR, the other is in Department OP, and division is null. This second bit of code only counts the first individual because I have job code 30801A, Department ES, Division HR in the job vacancy table, but not the other. It doesn't even show a record for Job Code 30801A, Department OP.
Your third bit of code gives 609 for every record again.

try this. I just realized your expression can be simplified because your parameters are all string parameters.

NumOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = [Job]![code]" & " And [MY - JobVacancyOccupants]![DivisionCode]=[JobVacancy]![DivisionCode]")

[mlcampeau]

try this. I just realized your expression can be simplified because your parameters are all string parameters.

NumOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = [Job]![code]" & " And [MY - JobVacancyOccupants]![DivisionCode]=[JobVacancy]![DivisionCode]")

Hmm, your code produces this message:
"The expression you entered as a query parameter produced this error: 'HRSource can't find the name 'Job!Code' you entered in the expression'"
Weird. I think it's got to do with the quotation marks. I'll play around a bit.

[mlcampeau]

try this. I just realized your expression can be simplified because your parameters are all string parameters.[code]
NumOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = [Job]!

" & " And [MY - JobVacancyOccupants]![DivisionCode]=[JobVacancy]![DivisionCode]")

That simplified expression probably wouldn't give me the correct results anyway since
[code]DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' And [MY - JobVacancyOccupants]![DivisionCode]='"&[JobVacancy]![DivisionCode]&"'") AS NumOccupants

doesn't and it's virtually the same expression. Well, as mentioned in previous posts, it gives the correct counts for most jobs, but leaves out entire records. I'm about ready to give up on this *sigh*

[puppydogbuddy]

That simplified expression probably wouldn't give me the correct results anyway since
[code]DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' And [MY - JobVacancyOccupants]![DivisionCode]='"&[JobVacancy]![DivisionCode]&"'") AS NumOccupants

doesn't and it's virtually the same expression. Well, as mentioned in previous posts, it gives the correct counts for most jobs, but leaves out entire records. I'm about ready to give up on this *sigh*

Don't give up yet. I was experimenting and have not provided for the nulls yet. I wanted you to tell me the output from simplified expression and will work with that.

[puppydogbuddy]

MLCampeau,
See if this works for you. I took a new tack here...I decided to assign a value of "NA" (not available) for any DivisionCode in the JobVacancy table that was null or blank (""). This revised DivisionCode (DivisionCodeR) was then fed into the DCount formula and treated as a real division in the count. If you want all the Jobs in the JobsVacancy table that do not have a DivisionCode to be counted as +1 in the count, then this works. If you want some of the Jobs from the JobVacancy table with no division code to be counted and others without a division code to not be counted, then this would have to be modified. Here is the entire select statement as revised.
[code]
SELECT [MY - JobVacancyOccupants].ID, [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].DeptCode, [MY - JobVacancyOccupants].DivisionCode, JobVacancy.DivisionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA",[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'") AS NumOccupants FROM [MY - JobVacancyOccupants] LEFT JOIN JobVacancy ON ([MY - JobVacancyOccupants].DivisionCode = JobVacancy.DivisionCode) AND ([MY - JobVacancyOccupants].DeptCode = JobVacancy.DeptCode) AND ([MY - JobVacancyOccupants].JobCode = JobVacancy.JobCode);
 

[mlcampeau]

MLCampeau,
See if this works for you. I took a new tack here...I decided to assign a value of "NA" (not available) for any DivisionCode in the JobVacancy table that was null or blank (""). This revised DivisionCode (DivisionCodeR) was then fed into the DCount formula and treated as a real division in the count. If you want all the Jobs in the JobsVacancy table that do not have a DivisionCode to be counted as +1 in the count, then this works. If you want some of the Jobs from the JobVacancy table with no division code to be counted and others without a division code to not be counted, then this would have to be modified. Here is the entire select statement as revised.
[code]
SELECT [MY - JobVacancyOccupants].ID, [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].DeptCode, [MY - JobVacancyOccupants].DivisionCode, JobVacancy.DivisionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA",[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'") AS NumOccupants FROM [MY - JobVacancyOccupants] LEFT JOIN JobVacancy ON ([MY - JobVacancyOccupants].DivisionCode = JobVacancy.DivisionCode) AND ([MY - JobVacancyOccupants].DeptCode = JobVacancy.DeptCode) AND ([MY - JobVacancyOccupants].JobCode = JobVacancy.JobCode);
 

Thanks for sticking with me on this. I tried the code you suggested but for some reason it's not liking the '"& [Job]![code] &"' because it asks for the parameter value. If I just press enter without inputting a value, it does the count, but it's not correct because it's not counting where the job codes match. I don't understand why it doesn't like that because that syntax was working in previous attempts....I also tried [Job].[code] and [Job]![code] without thhe '"& &"' and it gave #Error. ?????

[puppydogbuddy]

Thanks for sticking with me on this. I tried the code you suggested but for some reason it's not liking the '"& [Job]![code] &"' because it asks for the parameter value. If I just press enter without inputting a value, it does the count, but it's not correct because it's not counting where the job codes match. I don't understand why it doesn't like that because that syntax was working in previous attempts....I also tried [Job].[code] and [Job]![code] without thhe '"& &"' and it gave #Error. ?????

I know it works because I created a mimic on my end using the sample data you provided.

The problem on your end is probably the order that JobVacancy.DivisionCodeR got placed in the query by MS Access. Access probably placed it in the next vacant column, and I physically placed it right after JobVacancy.DivisionCode and right before NumOccupants. Also, make sure your query table grid shows the 3 left joins.

[mlcampeau]

I know it works because I created a mimic on my end using the sample data you provided.

The problem on your end is probably the order that JobVacancy.DivisionCodeR got placed in the query by MS Access. Access probably placed it in the next vacant column, and I physically placed it right after JobVacancy.DivisionCode and right before NumOccupants. Also, make sure your query table grid shows the 3 left joins.

Hmm...I tried [code=sql]SELECT [MY - JobVacancyOccupants].ID, [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].DepartmentCode, [MY - JobVacancyOccupants].DivisionCode, JobVacancy.DivisionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA",[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'") AS NumOccupants
FROM [MY - JobVacancyOccupants] LEFT JOIN JobVacancy ON ([MY - JobVacancyOccupants].DivisionCode = JobVacancy.DivisionCode) AND ([MY - JobVacancyOccupants].DepartmentCode = JobVacancy.DepartmenCode) AND ([MY - JobVacancyOccupants].JobCode = JobVacancy.JobCode);

and the order of my columns are: Empl ID; Job Code; Department Code; Division Code; JobVacancy.DivisionCode; DivisionCodeR; NumOccupants. It's asking twice for the value of [Job]![code]. And yes, there are three left joins in the query table grid. The other thing, I don't think it should be "[JobCode] = '" & [Job]![code] & "' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'"), it should be AND, because I need the job codes to match, as well as the division code. Naturally, since my query isn't recognizing [Job]![code], if I change it to AND I just get 0's.

[mlcampeau]

I changed [Job]![code] to [JobVacancy]![JobCode] to see what happens, and that works. It seems that the DCount isn't wanting to reference a table/field that isn't in the query table grid. I really want [Job]![code] to be used though, because not all job codes are in the job vacancy table.
As predicted in my previous post, AND would be correct, otherwise it is counting all employees whose division matches the JobVacancy.DivisionCode no mattter what their job code is. An example of what is showing up:
Emp ID; Job Code; Department Code; Division Code; JobVacancy.DivisionCode; DivisionCodeR; NumOccupants
1234; 30801A; OP; ; ; NA; 0
5678; 30801A; ES; HR; HR; HR; 1
It should be showing 1 as the NumOccupants for employee 1234.

[puppydogbuddy]

I changed [Job]![code] to [JobVacancy]![JobCode] to see what happens, and that works. It seems that the DCount isn't wanting to reference a table/field that isn't in the query table grid. I really want [Job]![code] to be used though, because not all job codes are in the job vacancy table.
As predicted in my previous post, AND would be correct, otherwise it is counting all employees whose division matches the JobVacancy.DivisionCode no mattter what their job code is. An example of what is showing up:
Emp ID; Job Code; Department Code; Division Code; JobVacancy.DivisionCode; DivisionCodeR; NumOccupants
1234; 30801A; OP; ; ; NA; 0
5678; 30801A; ES; HR; HR; HR; 1
It should be showing 1 as the NumOccupants for employee 1234.

One change I think you need to make is to uncheck EmpID as an selection item (uncheck the show box). The reason is that your query is about positions (filled or vacant), not about employees ...in fact if you try to join empID with vacancy table, it would not make sense.
.
Modifying my version of your query which I posted in post#73 to unshow the empID works on my end and correctly shows 2 NumOccupants for 30801A>>>>1 for Dept ES;Div HR + 1 for Dept OP;Div NA.

You need to understand what I did, and understand why, in my version "Or" is correct as opposed to "And".. Logically, your objective for this query is to account for filled positions + vacant positions to give you the total positions. Given that you need to join the Vacant positions and filled positions so that they equal the Total NumOccupants by JobCode and Department; given that some or all of the division codes are not shown (blank) on the JobVacancy table, I assigned a Division Code of "NA" meaning Not Available. Alternatively, you can think of the "Or" as a "+" if you want and revise your query as:follows in pseudo code. DCount of filled postions from [My -JobVacancyOccupants] plus DCount of vacant positions from [JobVacancy] results in total NumOccupants.....which, by the way, would be clearer if you called it NumPositions.

[mlcampeau]

One change I think you need to make is to uncheck EmpID as an selection item (uncheck the show box). The reason is that your query is about positions (filled or vacant), not about employees ...in fact if you try to join empID with vacancy table, it would not make sense.
.
Modifying my version of your query which I posted in post#73 to unshow the empID works on my end and correctly shows 2 NumOccupants for 30801A>>>>1 for Dept ES;Div HR + 1 for Dept OP;Div NA.

You need to understand what I did, and understand why, in my version "Or" is correct as opposed to "And".. Logically, your objective for this query is to account for filled positions + vacant positions to give you the total positions. Given that you need to join the Vacant positions and filled positions so that they equal the Total NumOccupants by JobCode and Department; given that some or all of the division codes are not shown (blank) on the JobVacancy table, I assigned a Division Code of "NA" meaning Not Available. Alternatively, you can think of the "Or" as a "+" if you want and revise your query as:follows in pseudo code. DCount of filled postions from [My -JobVacancyOccupants] plus DCount of vacant positions from [JobVacancy] results in total NumOccupants.....which, by the way, would be clearer if you called it NumPositions.

I agree about unchecking the EmpID show box. I have done that. I'm still having problems with the DCount referencing [Job]![code].
Looking at your pseudo code it makes me wonder if I have been super clear on what I am after. Here is your pseudo code:

DCount of vacant positions from [JobVacancy] results in total NumOccupants.....which, by the way, would be clearer if you called it NumPositions.

I am not actually looking for the number of positions. I have that number in the job vacancy table.
Job Vacancy
JobCode
Departmen
Division
NumberofPositions

I'm looking for a count of all employees associated with a job code in a particular department or division (NumOccupants). Essentially, I would like my query result to show every job title in the Job Table, and how many employees have that job in a particular department and division. So the layout would be like this:
Job Code; Department; Division; NumberofPositions; Number of Occupants;NumberofVacancies
I will then take JobVacancy.NumberofPositions - NumOccupants to calculate the NumberofVacancies.
There are no cases where a JobVacancy table record will be incomplete (i.e. - a blank Department or Division). There are cases, however, in the EmplOrganization table (which is one of the tables in the MY - JobVacancyOccupants query) where the department and/or division is not filled in for a particular employee, but their job code is. (I'm in the process of trying to fill in those gaps, but it's a complicated situation). So where my problem then comes in is that not every job code is entered in the job vacancy table, mainly because I don't know how many positions there are for every position, and which department and division the positions are in. An example is JobCode 30111A, Department BS, Division CORP. I have one occupant with this criteria, but this position is not listed in the job vacancy table. When I try your query:
[code=sql]SELECT [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].DepartmentCode, [MY - JobVacancyOccupants].DivisionCode, JobVacancy.DivisionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA",[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'") AS NumOccupants
FROM [MY - JobVacancyOccupants] LEFT JOIN JobVacancy ON ([MY - JobVacancyOccupants].JobCode = JobVacancy.JobCode) AND ([MY - JobVacancyOccupants].DepartmentCode = JobVacancy.DepartmenCode) AND ([MY - JobVacancyOccupants].DivisionCode = JobVacancy.DivisionCode);

as I said before, I get prompted for the [Job]![code] value twice. It then displays the query and gives me results like this:
30111A; BS; CORP; ; NA; 0
30800A; ES; HR; HR; HR; 7
30801A; OP; ; ; NA; 0
30801A; ES; HR; HR; HR; 7
31200A; MAN; DPS; DPS; DPS; 14
31200A; MAN; VAD; VAD; VAD; 9
31200A; MAN; SOM; SOM; SOM; 15

those results should actually look like this:
30111A; BS; CORP; ; NA; 1
30800A; ES; HR; HR; HR; 1
30801A; OP; ; ; NA; 1
30801A; ES; HR; HR; HR; 1
31200A; MAN; DPS; DPS; DPS; 1
31200A; MAN; VAD; VAD; VAD; 1
31200A; MAN; SOM; SOM; SOM; 1

So, as I mentioned before, it seems to be counting all employees that have the same division as the JobVacancy.DivisionCode, obviously because it's not registering what [Job]![code] is. I don't understand why it doesn't like that reference since it's worked in the past.

[mlcampeau]

I tried adding the Job table to the query grid, so it's no longer asking me for the parameter value of [Job]![code]:
[code=sql]SELECT [MY - JobVacancyOccupants].JobCode, [MY - JobVacancyOccupants].DepartmentCode, [MY - JobVacancyOccupants].DivisionCode, JobVacancy.DivisionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA",[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] ='"&[Job]!

&"' OR [MY - JobVacancyOccupants]![DivisionCode]='" & [DivisionCodeR] & "'") AS NumOccupants
FROM Job LEFT JOIN ([MY - JobVacancyOccupants] LEFT JOIN JobVacancy ON ([MY - JobVacancyOccupants].JobCode = JobVacancy.JobCode) AND ([MY - JobVacancyOccupants].DepartmentCode = JobVacancy.DepartmenCode) AND ([MY - JobVacancyOccupants].DivisionCode = JobVacancy.DivisionCode)) ON Job.Code = [MY - JobVacancyOccupants].JobCode;

With the OR in the DCount, I get results like this:
30111A; BS; CORP; ; NA; 1
30800A; ES; HR; HR; HR; 7
30801A; OP; ; ; NA; 2
30801A; ES; HR; HR; HR; 8
31200A; MAN; DPS; DPS; DPS; 25
31200A; MAN; VAD; VAD; VAD; 21
31200A; MAN; SOM; SOM; SOM; 27

but the results should be:
30111A; BS; CORP; ; NA; 1
30800A; ES; HR; HR; HR; 1
30801A; OP; ; ; NA; 1
30801A; ES; HR; HR; HR; 1
31200A; MAN; DPS; DPS; DPS; 2 (I made an error on an above post and said it should be 1, but it really should be 2)
31200A; MAN; VAD; VAD; VAD; 1
31200A; MAN; SOM; SOM; SOM; 1

When I change it to AND I get this:
30111A; BS; CORP; ; NA; 0
30800A; ES; HR; HR; HR; 1
30801A; OP; ; ; NA; 0
30801A; ES; HR; HR; HR; 1
31200A; MAN; DPS; DPS; DPS; 2
31200A; MAN; VAD; VAD; VAD; 1
31200A; MAN; SOM; SOM; SOM; 1

[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 - JobVacancyOccupants]
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 - JobVacancyOccupants]
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 - JobVacancyOccupants. 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 - JobVacancyOccupants 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 - JobVacancyOccupants), as well as the Number of positions, and number of occupants (Our DCount expression). Once I get that to work, then I can calculate NumberofPositions-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 NumberofVacancies > 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 MyVacancyOccupants) 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 MyVacancyOccupants) as the source for [ID] in the Dcount. .

That won't work because the Job table only has the following fields:
Code
EEOCategoryCode
EEOSubCategoryCode
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 - JobVacancyOccupants 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_JobVacancyOccupants2.Code, MY_JobVacAndJobTable.DepartmenCode, MY_JobVacAndJobTable.DivisionCode, MY_JobVacancyOccupants2.DepartmentCode, MY_JobVacancyOccupants2.DivisionCode, MY_JobVacAndJobTable.NumberofPositions, DCount("[ID]","[MY_JobVacancyOccupants2]","[code] ='" & [MY_JobVacAndJobTable]!

 & "' 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_JobVacAndJobTable and a right join leaves out records from MY_JobVacancyOccupants2. 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
EEOSubCategoryCode
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 - JobVacancyOccupants 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_JobVacancyOccupants2.Code, MY_JobVacAndJobTable.DepartmenCode, MY_JobVacAndJobTable.DivisionCode, MY_JobVacancyOccupants2.DepartmentCode, MY_JobVacancyOccupants2.DivisionCode, MY_JobVacAndJobTable.NumberofPositions, DCount("[ID]","[MY_JobVacancyOccupants2]","[code] ='" & [MY_JobVacAndJobTable]!

 & "' 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_JobVacAndJobTable and a right join leaves out records from MY_JobVacancyOccupants2. 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.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, 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.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, 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!JobVacanciesOnlySR.Report.Filter = "DivisionCode = 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=FSS. 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.

[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!JobVacanciesOnlySR.Report.Filter = "DivisionCode = 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=FSS. 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 = "DivisionCode = FSS"

To:
strFilter = "DivisionCode = '" & '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 (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) 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("[NumberofPositions]","JobVacancy", "[JobVacancy.JobCode]=[code]"))-DCount("[MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] =[code]"))<0,0,((DSum("[NumberofPositions]","JobVacancy", "[JobVacancy.JobCode]=[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=IIf([NumberofPositions]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnly]![code] AND [MY - ActiveEmpJob]![DivisionCode] = [MY - JobVacancies]![DivisionCode] "))<0,0,[NumberofPositions]-(DCount(" [MY - ActiveEmpJob]![ID]","MY - ActiveEmpJob"," [MY - ActiveEmpJob]![JobCode] = Reports![JobVacanciesOnly]!

 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