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!

[puppydogbuddy]

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!

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

[mlcampeau]

Thanks for the reply. As I said, I am very very new to VBA. I understand the concepts of the OpenReport command and the coding makes sense to me, but where do I put the code? Right now, I don't have a form with a button to open the report, I'm just opening it through the database window. Would it go in the OnOpen event of the report? I tried this:

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

but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies

[puppydogbuddy]

Thanks for the reply. As I said, I am very very new to VBA. I understand the concepts of the OpenReport command and the coding makes sense to me, but where do I put the code? Right now, I don't have a form with a button to open the report, I'm just opening it through the database window. Would it go in the OnOpen event of the report? I tried this:

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

but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies

If you are getting prompted to enter the parameters, you might try declaring the two parameters on the line preceding the beginning of the select statement that they appear so Access will know how to handle them and see if that helps: The syntax is illustrated below. Be sure and put their correct data type:

PARAMETERS [NumVacancies] Integer, [TotalVacancies] Integer;
Select xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

If that doesn't help:
Normally the OpenReport code is put behind a button, but it can be initiated from an event procedure using the report's OnOpen event like you did, except that I think the form open event might be too late to pass your parameters. It might work from the Form activate event, which precedes the form open event:

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

[mlcampeau]

If you are getting prompted to enter the parameters, you might try declaring the two parameters on the line preceding the beginning of the select statement that they appear so Access will know how to handle them and see if that helps: The syntax is illustrated below. Be sure and put their correct data type:

PARAMETERS [NumVacancies] Integer, [TotalVacancies] Integer;
Select xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

NumVacancies and TotalVacancies are not in any of my Select statements as they are calculated on the report in a textbox.

If that doesn't help:
Normally the OpenReport code is put behind a button, but it can be initiated from an event procedure using the report's OnOpen event like you did, except that I think the form open event might be too late to pass your parameters. It might work from the Form activate event, which precedes the form open event:

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

I tried placing the above code in the Activate event and it didn't ask me for parameters, but it also didn't filter anything out....

[puppydogbuddy]

NumVacancies and TotalVacancies are not in any of my Select statements as they are calculated on the report in a textbox.


I tried placing the above code in the Activate event and it didn't ask me for parameters, but it also didn't filter anything out....

Ok, put your code back in the Report Open event with the changed syntax for strWhere as shown below and let me know if that mad e a difference:

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

[mlcampeau]

Unfortunately it's still asking me to enter the parameter values :(

[puppydogbuddy]

Unfortunately it's still asking me to enter the parameter values :(

Ok, what are the names of the textboxes on the report that are holding the calcs for NumVacancies and TotalVacancies?

[mlcampeau]

Ok, what are the names of the textboxes on the report that are holding the calcs for NumVacancies and TotalVacancies?

The names are NumVacancies and TotalVacancies. I have triple checked the names because this doesn't seem like it should be such a difficult thing to do. The format is General Number for both of those textboxes.

[puppydogbuddy]

The names are NumVacancies and TotalVacancies. I have triple checked the names because this doesn't seem like it should be such a difficult thing to do. The format is General Number for both of those textboxes.

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

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

[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!YourSubreport![NumVacancies] > " & 0
also try the other syntax
strWhere = "Report!YourSubreport![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!YourSubreport![NumVacancies] > " & 0
also try the other syntax
strWhere = "Report!YourSubreport![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....the 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="Reports![JobVacanciesOnly]![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

[puppydogbuddy]

I meant subreport, not subform

Access processes the Open event before it runs the query that provides the report with its data, so I have not found any way to filter the report without creating an updated recordset from the report’s recordset that includes the effect of the parameters. Try this and let me know.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Routine
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef.
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("JobVacancyJobs")
    qdf.Parameters(1) = Me![TotalVacancies].Value > 0
    qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
    Set rs = qdf.OpenRecordset()
 
    rs.Close
 
DoCmd.Maximize
 
Exit_Continue:
    Set rs = Nothing
    Set qdf = Nothing
    Exit Sub
Error_Routine:
        MsgBox "Error " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
 

[mlcampeau]

Access processes the Open event before it runs the query that provides the report with its data, so I have not found any way to filter the report without creating an updated recordset from the report’s recordset that includes the effect of the parameters. Try this and let me know.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Error_Routine
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef.
 
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("JobVacancyJobs")
    qdf.Parameters(1) = Me![TotalVacancies].Value > 0
    qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
    Set rs = qdf.OpenRecordset()
 
    rs.Close
 
DoCmd.Maximize
 
Exit_Continue:
    Set rs = Nothing
    Set qdf = Nothing
    Exit Sub
Error_Routine:
        MsgBox "Error " & Err.Number & " " & Err.Description
        Resume Exit_Continue
End Sub
 

I tried your code and I get an Error: "Error 3265 Item not found in this collection"

[mlcampeau]

I tried your code and I get an Error: "Error 3265 Item not found in this collection"

Okay I made an adjustment to your code because it did not have the proper query name.

Set qdf = db.QueryDefs("MY - JobVacancyJobs")

(main report based on that query) which only has the fields Job Code, Job Title, Job Grade, Job Level, Job Group, and I got an error so I tried

Set qdf = db.QueryDefs("MY - JobVacancies")

(subreport based on that query) which has the fields JobCode, DepartmentCode, DivisionCode, NumberofPositions and I get the same error as the above code: Error 2427 You entered an expression that has no value.
Is this because TotalVacancies and NumVacancies are not actually in the query, but calculated on the report/subreport? When I click OK, it goes to my report, but doesn't filter anything.

[puppydogbuddy]

Okay I made an adjustment to your code because it did not have the proper query name.

Set qdf = db.QueryDefs("MY - JobVacancyJobs")

(main report based on that query) which only has the fields Job Code, Job Title, Job Grade, Job Level, Job Group, and I got an error so I tried

Set qdf = db.QueryDefs("MY - JobVacancies")

(subreport based on that query) which has the fields JobCode, DepartmentCode, DivisionCode, NumberofPositions and I get the same error as the above code: Error 2427 You entered an expression that has no value.
Is this because TotalVacancies and NumVacancies are not actually in the query, but calculated on the report/subreport? When I click OK, it goes to my report, but doesn't filter anything.

No, the recordset should reflect the effect of querydef and parameter. try eliminating parameter 2 (line 11) and see if main report reflects parameter 1.

[mlcampeau]

No, the recordset should reflect the effect of querydef and parameter. try eliminating parameter 2 (line 11) and see if main report reflects parameter 1.

Removing parameter 2 gives the same error: Error 2427 You entered an expression that has no value.
Removing parameter 1 and changing parameter 2 to parameter 1 gives Error 2455 You entered an expression that has an invalid reference to the property Form/Report

qdf.Parameters(1) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0

[puppydogbuddy]

My mistake, I meant for you to remove the parameter for the subreport not the one for the main report.

[mlcampeau]

My mistake, I meant for you to remove the parameter for the subreport not the one for the main report.

Sorry, I mustn't have been clear in that last post. I removed the parameter for the subreport and it gave me the Error 2427 You entered an expression that has no value.
When that didn't succeed, I tried removing the parameter for the main report instead, and that gave me the Error 2455 You entered an expression that has an invalid reference to the property Form/Report.

[mlcampeau]

Would it be any easier to do the calculation of TotalVacancies and NumberofVacancies in VBA rather than referring to the textbox on the report? I'd probably still have the same problem of the queries not executing first, though, wouldn't I?

[puppydogbuddy]

Would it be any easier to do the calculation of TotalVacancies and NumberofVacancies in VBA rather than referring to the textbox on the report? I'd probably still have the same problem of the queries not executing first, though, wouldn't I?

Yes. The easiest way would be to include it in each of your select statements. Strangely enough, per this link, you can include a textbox in your select statement...the link uses a textbox on a form as an example but I think it could be a textbox on a report also.

see comment by Duane Hookum
http://www.tek-tips.com/viewthread.c...361948&page=15

I am still researching this, but you are welcome to try the method outilined in the above link, including your report textbox in each of the select statement as illustrated by Hookum in the above link with the selection list inclusion of the textbox from a form. Be sure and include the full reference to your textboxes, e.g. Reports![JobVacanciesOnly]![TotalVacancies].Value > 0......

[mlcampeau]

Yes. The easiest way would be to include it in each of your select statements. Strangely enough, per this link, you can include a textbox in your select statement...the link uses a textbox on a form as an example but I think it could be a textbox on a report also.

see comment by Duane Hookum
http://www.tek-tips.com/viewthread.c...361948&page=15

I am still researching this, but you are welcome to try the method outilined in the above link, including your report textbox in each of the select statement as illustrated by Hookum in the above link with the selection list inclusion of the textbox from a form. Be sure and include the full reference to your textboxes, e.g. Reports![JobVacanciesOnly]![TotalVacancies].Value > 0......

Okay, now my question is this. I created my queries in the query design view, not in vba. Does that make a difference? Would I just add the textbox reference to my already existing query or should I have created the report based on a query written in vba?

[mlcampeau]

Okay, now my question is this. I created my queries in the query design view, not in vba. Does that make a difference? Would I just add the textbox reference to my already existing query or should I have created the report based on a query written in vba?

Changing my MY - JobVacancies query to:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
WHERE Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]>0
GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

and my MY - JobVacancyJobs query to:

SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc, Reports!JobVacanciesOnly![TotalVacancies] AS TotalVac
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
WHERE Reports!JobVacanciesOnly![TotalVacancies]>0
ORDER BY Job.Title, Job.Code;

doesn't cause any errors. The report opens, but doesn't display any info. All fields on the main report are blank except Title which shows #Error and nothing on the subreport displays.

[puppydogbuddy]

Whether you did it in design view on the query grid or used VBA should not have made any difference because Access will convert the grid to executable sql. I assume that your ReportOpen code just used the basic DoCmd.Open report syntax. Try commenting out all your ReportOpen code and open the report manually by clicking on the report itelf from the Access Report Window.

I will do some more research tomorrow. I know there are other alternatives, like creating NumVacancies and TotalVacancies as alias items in the applicable query instead of referencing to the textbox. To do this go to your query grid for each select statement, create an alias column as shown:
NumVacancies:DLookup......blah...blah
then like before, put > 0 in the criteria row. This method should definitely work because it should show the results in your query, when you run the query by itself.

[mlcampeau]

I assume that your ReportOpen code just used the basic DoCmd.Open report syntax. Try commenting out all your ReportOpen code and open the report manually by clicking on the report itelf from the Access Report Window.

I do not have any ReportOpen code. I open the report from the Access Report Window right now and do not have any forms or buttons to click in order to open it.

[puppydogbuddy]

I do not have any ReportOpen code. I open the report from the Access Report Window right now and do not have any forms or buttons to click in order to open it.

Ok, then. did you try the alias column in query method I suggested in the same post?

[mlcampeau]

I know there are other alternatives, like creating NumVacancies and TotalVacancies as alias items in the applicable query instead of referencing to the textbox. To do this go to your query grid for each select statement, create an alias column as shown:
NumVacancies:DLookup......blah...blah
then like before, put > 0 in the criteria row. This method should definitely work because it should show the results in your query, when you run the query by itself.

I can't seem to get the DLookup to work proper. I tried Dlookup, but then changed it to DCount (since I want the number to do the math) and I'm getting the same problem. I tried the following code in order to get the total number of occupants for each position in each division so that I can subtract it from the Number of Positions, in order to get the number of vacancies:
TotalOccupants: DCount("[MY - JobVacancyOccupants]![ID]","MY - JobVacancyOccupants")
That code gives me a number of 610 for every record in the query. (It's basically counting every employee once for each and every job title in the database) I then tried adding criteria in the code:
TotalOccupants: DCount("[MY - JobVacancyOccupants]![ID]","MY - JobVacancyOccupants","[MY - JobVacancyOccupants]![JobCode]=[Job]![code]")
I get an error: The expression you entered as a query parameter produced this error: 'HRSource can't find the name 'Job!Code' you entered in the expression'.
I tried different syntax like [Job].[code], Code, Job.Code and they all give the same error. Same thing happens if I try to add criteria regarding the division.
This is starting to get sooo frustrating!! As smart as computers are, I wish they were a little bit smarter! Or maybe it's me that should be a little bit smarter....

[puppydogbuddy]

I can't seem to get the DLookup to work proper. I tried Dlookup, but then changed it to DCount (since I want the number to do the math) and I'm getting the same problem. I tried the following code in order to get the total number of occupants for each position in each division so that I can subtract it from the Number of Positions, in order to get the number of vacancies:
TotalOccupants: DCount("[MY - JobVacancyOccupants]![ID]","MY - JobVacancyOccupants")
That code gives me a number of 610 for every record in the query. (It's basically counting every employee once for each and every job title in the database) I then tried adding criteria in the code:
TotalOccupants: DCount("[MY - JobVacancyOccupants]![ID]","MY - JobVacancyOccupants","[MY - JobVacancyOccupants]![JobCode]=[Job]![code]")
I get an error: The expression you entered as a query parameter produced this error: 'HRSource can't find the name 'Job!Code' you entered in the expression'.
I tried different syntax like [Job].[code], Code, Job.Code and they all give the same error. Same thing happens if I try to add criteria regarding the division.
This is starting to get sooo frustrating!! As smart as computers are, I wish they were a little bit smarter! Or maybe it's me that should be a little bit smarter....

LOL!! It can be frustrating at times, but if you have patience you will find the solution.

I am not sure why you need the second expression referenced above, because whatever level [ID] is on is the level that you will get the output. Nevertheless, I revised the second expression, assuming that Code is numeric, and also reivised assuming that [code] is a text data type.

By the way, it is not good practice to have spaces and hyphens in object names because Access will require references to these names to be enclosed with brackets. A better practice for creating Names should be like this:
MyJobVacancyOccupants
or
My_JobVacancyOccupants

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]")

Revised syntax for your second expression, assuming that [code] is numeric.
TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[code] = “ & [Job]![code])

Revised syntax for your second expression, assuming that [code] is text.
TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[code] = “ & [Job]![code] & “’”

[mlcampeau]

Thanks for the info on the names of my queries. I wasn't planning on keeping the names that way - I was just trying to keep track of the queries that I had added to this database so they were easier for me to locate so I started them all with my initials.
As for the code...

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]")

gives me 610 in every record (so 610 occupants for each and every job code in the database)
I tried changing that to
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[code] = “ & [Job]!

)&"'")

in order to try get the DCount to count the ID's where the Job Code in the MY - JobVacancyOccupants query matches the job code in the Job table. I get a message box asking me to enter the parameter value for Job!Code.
The rest of my sql query looks like this:

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
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

*sigh* I just don't know! Oh, and Job Code is a text field

[puppydogbuddy]

Thanks for the info on the names of my queries. I wasn't planning on keeping the names that way - I was just trying to keep track of the queries that I had added to this database so they were easier for me to locate so I started them all with my initials.
As for the code...

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]")

gives me 610 in every record (so 610 occupants for each and every job code in the database)
I tried changing that to
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[code] = “ & [Job]!

)&"'")

in order to try get the DCount to count the ID's where the Job Code in the MY - JobVacancyOccupants query matches the job code in the Job table. I get a message box asking me to enter the parameter value for Job!Code.
The rest of my sql query looks like this:

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
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

*sigh* I just don't know! Oh, and Job Code is a text field

We'll get there. Looked at your query and realized it is JobCode not Job!Code
so, try this:

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"'")

[mlcampeau]

We'll get there. Looked at your query and realized it is JobCode not Job!Code
so, try this:

TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"'")

Okay, well, this just proved that I'm losing my mind. Completely ignore my last post because I was putting the information in the wrong query!! That query is what my subreport is based on, and I'm trying to work on the TotalOccupants based on the query of the main report. So.....When I try
[code]TotalOccupants: DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 &"'")

low and behold, it gives me the total occupants for that position!! Now I just need to figure out the correct expression to subtract that from JobVacancy.NumberofPositions.
And, FYI, the query for the main report is now
[code=sql]SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]!

 & "'") AS TotalOccupants
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;

[mlcampeau]

[code=sql]SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]![code] & "'") AS TotalOccupants, DSum("[NumberofPositions]","JobVacancy","[JobVacancy]![JobCode]='" & [Job]!

 & "'") AS TotalPositions, [TotalPositions]-[TotalOccupants] AS TotalVacancies
FROM Grade RIGHT JOIN (EEOSubCategory RIGHT JOIN (EEOCategory RIGHT JOIN Job ON EEOCategory.Code = Job.EEOCategoryCode) ON EEOSubCategory.Code = Job.EEOSubCategoryCode) ON Grade.Code = Job.Grade
ORDER BY Job.Title, Job.Code;

You have NO idea how long I tried making a query that would give me the accurate numbers for TotalOccupants, TotalPositions, TotalVacancies!! That's why I was calculating them on the report! But it works!!!! Now I need to try doing that with the query for the subform (it'll probably be a bit more difficult cuz the divisions need to match) But thank you thank you thank you for getting me this far!!

[puppydogbuddy]

[code=sql]SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [Job]![code] & "'") AS TotalOccupants, DSum("[NumberofPositions]","JobVacancy","[JobVacancy]![JobCode]='" & [Job]!

 & "'") AS TotalPositions, [TotalPositions]-[TotalOccupants] AS TotalVacancies
FROM Grade RIGHT JOIN (EEOSubCategory RIGHT JOIN (EEOCategory RIGHT JOIN Job ON EEOCategory.Code = Job.EEOCategoryCode) ON EEOSubCategory.Code = Job.EEOSubCategoryCode) ON Grade.Code = Job.Grade
ORDER BY Job.Title, Job.Code;

You have NO idea how long I tried making a query that would give me the accurate numbers for TotalOccupants, TotalPositions, TotalVacancies!! That's why I was calculating them on the report! But it works!!!! Now I need to try doing that with the query for the subform (it'll probably be a bit more difficult cuz the divisions need to match) But thank you thank you thank you for getting me this far!!

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

[mlcampeau]

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

Okay, the quotes are really screwing me up.

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

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

[mlcampeau]

Okay, the quotes are really screwing me up.

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

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

So I came up with this:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

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

[puppydogbuddy]

So I came up with this:

SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] &"' AND [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "'") AS NumOccupants
FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;

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

Ok, Try this:

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

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

Let me know..

[mlcampeau]

Ok, Try this:

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

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

Let me know..

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

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

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

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

[puppydogbuddy]

is division code numeric or text?

[mlcampeau]

is division code numeric or text?

DivisionCode is a text field

[puppydogbuddy]

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

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

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

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

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

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

[mlcampeau]

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

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

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

[puppydogbuddy]

Longest thread.......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.