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]

change strWhere to this (for now, see if it works for one textbox):

strWhere = "Me![NumVacancies] > " & 0

:( nope, still asks me to enter the parameter. I also tried
strWhere = "Me![NumVacancies] > 0"

[puppydogbuddy]

:( nope, still asks me to enter the parameter. I also tried
strWhere = "Me![NumVacancies] > 0"

I believe numVacancies is in your subreport. If so replace Me with a fully qualified reference to your subreport as illustrated below:
strWhere = "Report!YourSub report![NumVacancies] > " & 0
also try the other syntax
strWhere = "Report!YourSub report![NumVacancies] > 0"

[mlcampeau]

I believe numVacancies is in your subreport. If so replace Me with a fully qualified reference to your subreport as illustrated below:
strWhere = "Report!YourSub report![NumVacancies] > " & 0
also try the other syntax
strWhere = "Report!YourSub report![NumVacancies] > 0"

Nope, still asks for parameters. I tried:

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

as well as the other syntax. I am putting this code in the On Open event of the main report. Should it be this difficult??

[puppydogbuddy]

Nope, still asks for parameters. I tried:

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

as well as the other syntax. I am putting this code in the On Open event of the main report. Should it be this difficult??

It isn't difficult....th e syntax is just tricky when a subreport is involved....and I don't have the luxury of having the application in front of me to see what is happening.

Try changing the fully qualified reference to the subreport as follows, using the actual names of the objects in your application:

Reports![main report name]![subreport control name].Report![control name]

this is the recommended syntax for a fully qualified reference to a control on a subreport. See the link below:

http://support.microsoft.com/kb/209099

[mlcampeau]

Okay I just tried:

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

(I'm assuming Subreport control name is the name of my subreport...) It displays only the Main report portion of the report, but without any of the controls displayed. It only shows my labels and it says #Error where my job title should be showing, the rest are all blanks, except for one of my calculated value text boxes (counts the number of employees with a particular job title). It just shows 0 for that value and only the 1 page displays, when there are actually 87 pages to my report. Nothing on my subreport displays.
TotalVacancies is on the main report. That part should be fairly simple to filter, shouldn't it?

[mlcampeau]

Putting

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

in the On Activate event displays my entire report, but it doesn't filter it. :(
I also tried
strWhere="Repor ts![JobVacanciesOnl y]![TotalVacancies]>0"
and that doesn't filter it either

[mlcampeau]

hmm...searching on the net I came across this link http://p2p.wrox.com/archive/access/2002-09/148.asp where it says

You may want to put your code in the On Format or On Print events for the section (Detail, Header, Footer) where the text box resides.
During the On Open event there really isn't anything on the report, I suspect this is why you are getting the message.

in response to someone's question regarding the on open event. Would this be the reason why my report won't filter? Because the TotalVacancies and NumVacancies aren't being calculated until the report is opened. If this is the case, I really have no idea what the On Format or On Print are, so i'm not sure which one would be more suitable for me or how the code would work because I assume it wouldn't be

DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere

[puppydogbuddy]

hmm...searching on the net I came across this link http://p2p.wrox.com/archive/access/2002-09/148.asp where it says
in response to someone's question regarding the on open event. Would this be the reason why my report won't filter? Because the TotalVacancies and NumVacancies aren't being calculated until the report is opened. If this is the case, I really have no idea what the On Format or On Print are, so i'm not sure which one would be more suitable for me or how the code would work because I assume it wouldn't be

DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere

Firstly, if you did not use the strWhere to filter the report, your openReport command syntax would look like this:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenReport "JobVacanciesOnly", acViewPreview
End Sub
 

The statement made in the link you provided is something that I have heard of, but not experienced before. It makes sense only because the OnFormat and OnPrint events occur earlier than the OnOpen event because they are events that occur for the various sections of the report (header, detail, or footer) that the textbox resides. To get to an event procedure for onPrint and OnFormat, you would highlight the section that the textbox is located, then go to the event tab and there you will see the events for the section.

I need you to tell me what sections of the report/subreport NumVacancies and TotalVacancies are in.

[mlcampeau]

I need you to tell me what sections of the report/subreport NumVacancies and TotalVacancies are in.

TotalVacancies is in the Code Header of the main report and NumVacancies is in the Detail section of the subform.

[mlcampeau]

TotalVacancies is in the Code Header of the main report and NumVacancies is in the Detail section of the subform.

I meant subreport, not subform