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]

try this. I just realized your expression can be simplified because your parameters are all string parameters.[code]
NumOccupants: DCount("[ID]","[MY - JobVacancyOccup ants]","[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 - JobVacancyOccup ants]","[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 - JobVacancyOccup ants]","[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 - JobVacancyOccup ants].ID, [MY - JobVacancyOccup ants].JobCode, [MY - JobVacancyOccup ants].DeptCode, [MY - JobVacancyOccup ants].DivisionCode, JobVacancy.Divi sionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA" ,[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccup ants]","[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 - JobVacancyOccup ants].ID, [MY - JobVacancyOccup ants].JobCode, [MY - JobVacancyOccup ants].DeptCode, [MY - JobVacancyOccup ants].DivisionCode, JobVacancy.Divi sionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA" ,[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccup ants]","[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.Divi sionCodeR 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.Divi sionCode 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.Divi sionCodeR 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.Divi sionCode and right before NumOccupants. Also, make sure your query table grid shows the 3 left joins.

Hmm...I tried [code=sql]SELECT [MY - JobVacancyOccup ants].ID, [MY - JobVacancyOccup ants].JobCode, [MY - JobVacancyOccup ants].DepartmentCode , [MY - JobVacancyOccup ants].DivisionCode, JobVacancy.Divi sionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA" ,[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccup ants]","[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.Divi sionCode; 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 - JobVacancyOccup ants]![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.Divi sionCode no mattter what their job code is. An example of what is showing up:
Emp ID; Job Code; Department Code; Division Code; JobVacancy.Divi sionCode; 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.Divi sionCode no mattter what their job code is. An example of what is showing up:
Emp ID; Job Code; Department Code; Division Code; JobVacancy.Divi sionCode; 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 -JobVacancyOccup ants] 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 -JobVacancyOccup ants] 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
NumberofPositio ns

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; NumberofPositio ns; Number of Occupants;Numbe rofVacancies
I will then take JobVacancy.Numb erofPositions - NumOccupants to calculate the NumberofVacanci es.
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 EmplOrganizatio n table (which is one of the tables in the MY - JobVacancyOccup ants 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 - JobVacancyOccup ants].JobCode, [MY - JobVacancyOccup ants].DepartmentCode , [MY - JobVacancyOccup ants].DivisionCode, JobVacancy.Divi sionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA" ,[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccup ants]","[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.Divi sionCode, 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 - JobVacancyOccup ants].JobCode, [MY - JobVacancyOccup ants].DepartmentCode , [MY - JobVacancyOccup ants].DivisionCode, JobVacancy.Divi sionCode, IIf(Nz([JobVacancy].[DivisionCode],"")="","NA" ,[JobVacancy].[DivisionCode]) AS DivisionCodeR, DCount("[ID]","[MY - JobVacancyOccup ants]","[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