By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,319 Members | 2,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,319 IT Pros & Developers. It's quick & easy.

Filter report based on values of calculated value on report

Expert 100+
P: 296
I have a report (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) that are based on two separate queries.
MY - JobVacancyJobs
Expand|Select|Wrap|Line Numbers
  1. SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc
  2. 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
  3. ORDER BY Job.Title, Job.Code;
MY - JobVacancies
Expand|Select|Wrap|Line Numbers
  1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  4. 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] =
Expand|Select|Wrap|Line Numbers
  1. ")))
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]!
Expand|Select|Wrap|Line Numbers
  1.  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!
Jul 27 '07 #1
Share this Question
Share on Google+
94 Replies


puppydogbuddy
Expert 100+
P: 1,923
I have a report (JobVacanciesOnly) that has a subreport (JobVacanciesOnlySR) that are based on two separate queries.
MY - JobVacancyJobs
Expand|Select|Wrap|Line Numbers
  1. SELECT Job.Code, Job.Title, Job.Grade, Grade.Minimum, Grade.Midpoint, Grade.Maximum, Job.EEOCategoryCode, EEOCategory.Desc, Job.EEOSubCategoryCode, EEOSubCategory.Desc
  2. 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
  3. ORDER BY Job.Title, Job.Code;
MY - JobVacancies
Expand|Select|Wrap|Line Numbers
  1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  4. 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] =
Expand|Select|Wrap|Line Numbers
  1. ")))
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]!
Expand|Select|Wrap|Line Numbers
  1.  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
Jul 27 '07 #2

Expert 100+
P: 296
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. End Sub
but then when I try to open the report, it wants me to fill in the parameters for NumVacancies and TotalVacancies
Jul 27 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2. Dim strWhere As String
  3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. End Sub
Jul 28 '07 #4

Expert 100+
P: 296
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2. Dim strWhere As String
  3. strWhere = "[NumVacancies] > 0 or [TotalVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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....
Jul 30 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "[NumVacancies] > " & 0 & " or [TotalVacancies] > " & 0
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. End Sub
  6.  
Jul 30 '07 #6

Expert 100+
P: 296
Unfortunately it's still asking me to enter the parameter values :(
Jul 30 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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?
Jul 30 '07 #8

Expert 100+
P: 296
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.
Jul 30 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
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
Jul 30 '07 #10

Expert 100+
P: 296
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"
Jul 30 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
:( 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"
Jul 30 '07 #12

Expert 100+
P: 296
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "Report!JobVacanciesOnlySR![NumVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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??
Jul 30 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
Nope, still asks for parameters. I tried:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "Report!JobVacanciesOnlySR![NumVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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
Jul 30 '07 #14

Expert 100+
P: 296
Okay I just tried:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim strWhere As String
  3. strWhere = "Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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?
Jul 30 '07 #15

Expert 100+
P: 296
Putting
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2. Dim strWhere As String
  3. strWhere = "[TotalVacancies] > 0"
  4. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
  5. 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
Jul 30 '07 #16

Expert 100+
P: 296
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview, , strWhere
Jul 30 '07 #17

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. DoCmd.OpenReport "JobVacanciesOnly", acViewPreview
  3. End Sub
  4.  
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.
Jul 31 '07 #18

Expert 100+
P: 296
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.
Jul 31 '07 #19

Expert 100+
P: 296
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
Jul 31 '07 #20

puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo Error_Routine
  3.  
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim qdf As DAO.QueryDef.
  7.  
  8.     Set db = CurrentDb()
  9.     Set qdf = db.QueryDefs("JobVacancyJobs")
  10.     qdf.Parameters(1) = Me![TotalVacancies].Value > 0
  11.     qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
  12.     Set rs = qdf.OpenRecordset()
  13.  
  14.     rs.Close
  15.  
  16. DoCmd.Maximize
  17.  
  18. Exit_Continue:
  19.     Set rs = Nothing
  20.     Set qdf = Nothing
  21.     Exit Sub
  22. Error_Routine:
  23.         MsgBox "Error " & Err.Number & " " & Err.Description
  24.         Resume Exit_Continue
  25. End Sub
  26.  
Aug 1 '07 #21

Expert 100+
P: 296
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. On Error GoTo Error_Routine
  3.  
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim qdf As DAO.QueryDef.
  7.  
  8.     Set db = CurrentDb()
  9.     Set qdf = db.QueryDefs("JobVacancyJobs")
  10.     qdf.Parameters(1) = Me![TotalVacancies].Value > 0
  11.     qdf.Parameters(2) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
  12.     Set rs = qdf.OpenRecordset()
  13.  
  14.     rs.Close
  15.  
  16. DoCmd.Maximize
  17.  
  18. Exit_Continue:
  19.     Set rs = Nothing
  20.     Set qdf = Nothing
  21.     Exit Sub
  22. Error_Routine:
  23.         MsgBox "Error " & Err.Number & " " & Err.Description
  24.         Resume Exit_Continue
  25. End Sub
  26.  
I tried your code and I get an Error: "Error 3265 Item not found in this collection"
Aug 1 '07 #22

Expert 100+
P: 296
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 1 '07 #23

puppydogbuddy
Expert 100+
P: 1,923
Okay I made an adjustment to your code because it did not have the proper query name.
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 1 '07 #24

Expert 100+
P: 296
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
Expand|Select|Wrap|Line Numbers
  1. qdf.Parameters(1) = Me![JobVacanciesOnlySR].Report![NumVacancies].Value > 0
Aug 1 '07 #25

puppydogbuddy
Expert 100+
P: 1,923
My mistake, I meant for you to remove the parameter for the subreport not the one for the main report.
Aug 1 '07 #26

Expert 100+
P: 296
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.
Aug 1 '07 #27

Expert 100+
P: 296
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?
Aug 1 '07 #28

puppydogbuddy
Expert 100+
P: 1,923
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......
Aug 1 '07 #29

Expert 100+
P: 296
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?
Aug 1 '07 #30

Expert 100+
P: 296
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. WHERE Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]>0
  4. GROUP BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc, Reports![JobVacanciesOnly]![JobVacanciesOnlySR].Report![NumVacancies]
  5. ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;
and my MY - JobVacancyJobs query to:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE Reports!JobVacanciesOnly![TotalVacancies]>0
  4. 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.
Aug 1 '07 #31

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 1 '07 #32

Expert 100+
P: 296
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.
Aug 2 '07 #33

puppydogbuddy
Expert 100+
P: 1,923
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?
Aug 2 '07 #34

Expert 100+
P: 296
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....
Aug 2 '07 #35

puppydogbuddy
Expert 100+
P: 1,923
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] & “’”
Aug 2 '07 #36

Expert 100+
P: 296
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...
Expand|Select|Wrap|Line Numbers
  1. 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]!
Expand|Select|Wrap|Line Numbers
  1. )&"'")
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. ORDER BY JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode;
*sigh* I just don't know! Oh, and Job Code is a text field
Aug 2 '07 #37

puppydogbuddy
Expert 100+
P: 1,923
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...
Expand|Select|Wrap|Line Numbers
  1. 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]!
Expand|Select|Wrap|Line Numbers
  1. )&"'")
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT JobVacancy.JobCode, JobVacancy.DepartmenCode, JobVacancy.DivisionCode, JobVacancy.NumberofPositions, Department.Desc, Division.Desc
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. 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] &"'")
Aug 2 '07 #38

Expert 100+
P: 296
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]!
Expand|Select|Wrap|Line Numbers
  1.  &"'")
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]!
Expand|Select|Wrap|Line Numbers
  1.  & "'") AS TotalOccupants
  2. 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
  3. ORDER BY Job.Title, Job.Code;
Aug 2 '07 #39

Expert 100+
P: 296
[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]!
Expand|Select|Wrap|Line Numbers
  1.  & "'") AS TotalPositions, [TotalPositions]-[TotalOccupants] AS TotalVacancies
  2. 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
  3. 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!!
Aug 2 '07 #40

puppydogbuddy
Expert 100+
P: 1,923
[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]!
Expand|Select|Wrap|Line Numbers
  1.  & "'") AS TotalPositions, [TotalPositions]-[TotalOccupants] AS TotalVacancies
  2. 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
  3. 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!
Aug 2 '07 #41

Expert 100+
P: 296
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.
Expand|Select|Wrap|Line Numbers
  1. 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!!
Aug 2 '07 #42

Expert 100+
P: 296
Okay, the quotes are really screwing me up.
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. 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?
Aug 2 '07 #43

puppydogbuddy
Expert 100+
P: 1,923
So I came up with this:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM (JobVacancy LEFT JOIN Department ON JobVacancy.DepartmenCode = Department.Code) LEFT JOIN Division ON JobVacancy.DivisionCode = Division.Code
  3. 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..
Aug 2 '07 #44

Expert 100+
P: 296
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
Expand|Select|Wrap|Line Numbers
  1.  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........
Aug 2 '07 #45

puppydogbuddy
Expert 100+
P: 1,923
is division code numeric or text?
Aug 2 '07 #46

Expert 100+
P: 296
is division code numeric or text?
DivisionCode is a text field
Aug 2 '07 #47

puppydogbuddy
Expert 100+
P: 1,923
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
Expand|Select|Wrap|Line Numbers
  1.  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"
Expand|Select|Wrap|Line Numbers
  1. DCount("[ID]","[MY - JobVacancyOccupants]","[JobCode] = '" & [JobVacancy]![JobCode] & "' AND (IsNull([MY - JobVacancyOccupants]![DivisionCode]) OR IsNull([JobVacancy]![DivisionCode]) OR [MY - JobVacancyOccupants]![DivisionCode]='" & [JobVacancy]![DivisionCode] & "')") AS NumOccupants
Aug 2 '07 #48

Expert 100+
P: 296
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"
Expand|Select|Wrap|Line Numbers
  1. 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!
Aug 2 '07 #49

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 2 '07 #50

94 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.