If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
subreport repeating parameters
dazed and confused here....
i have two subreports that i added to a blank report. they each run off a different query. one gives me totals of all results of audited cases. the other gives me the details of those cases. the problem is, when i run the report, it asks me twice for the parameters. both queries have the same parameters. what am i missing? -- terri |
#2
|
|||
|
|||
subreport repeating parameters
terri wrote:
dazed and confused here.... i have two subreports that i added to a blank report. they each run off a different query. one gives me totals of all results of audited cases. the other gives me the details of those cases. the problem is, when i run the report, it asks me twice for the parameters. both queries have the same parameters. what am i missing? If you're building the report off parameterized queries, you can do one of two things: 1. remove the parameters and pass a valid WHERE clause in the OpenReport command 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
subreport repeating parameters
sorry...really not good at this.
could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri "PieterLinden via AccessMonster.com" wrote: terri wrote: dazed and confused here.... i have two subreports that i added to a blank report. they each run off a different query. one gives me totals of all results of audited cases. the other gives me the details of those cases. the problem is, when i run the report, it asks me twice for the parameters. both queries have the same parameters. what am i missing? If you're building the report off parameterized queries, you can do one of two things: 1. remove the parameters and pass a valid WHERE clause in the OpenReport command 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via http://www.accessmonster.com . |
#4
|
|||
|
|||
subreport repeating parameters
You won't be able to do that in this case as the parameters relate to the
queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 |
#5
|
|||
|
|||
subreport repeating parameters
Ken,
I am hoping you can help me since my issue is pretty much the same. I have two queries, both with date range parameters. When opening the report, with the subreport, I have to enter the parameters once for each query. I followed your instructions to the point of entering the parameters. Where/how do I enter the parameters. Do they go on the report or on the query? Each of my queries have different field dates. One query returns call records by date and the other query returns hours worked and contracts in/out by date. Any help would be appreciated. Thanks. Lori "KenSheridan via AccessMonster.com" wrote: You won't be able to do that in this case as the parameters relate to the queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 . |
#6
|
|||
|
|||
subreport repeating parameters
Did you create a form with the textbox controls and then enter the dates into
the textbox controls as Ken suggested? The form must remain open to run the report. If you do as suggested you should not be prompted at all for the dates. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Ken, I am hoping you can help me since my issue is pretty much the same. I have two queries, both with date range parameters. When opening the report, with the subreport, I have to enter the parameters once for each query. I followed your instructions to the point of entering the parameters. Where/how do I enter the parameters. Do they go on the report or on the query? Each of my queries have different field dates. One query returns call records by date and the other query returns hours worked and contracts in/out by date. Any help would be appreciated. Thanks. Lori "KenSheridan via AccessMonster.com" wrote: You won't be able to do that in this case as the parameters relate to the queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 . |
#7
|
|||
|
|||
subreport repeating parameters
Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes
one for start date and one for end date. There is also a button that is set to run the report. I open the form, enter the dates and click the button. I then get an Enter Parameter Value dialog box Start Date and End Date. I think I know where to look for the error, but am not sure what to look for. When running each query directly the first query is fine, but the second query is prompting for a Start and End Date. I believe I entered the parameters the same in each query and have compared them and do not see any differences, except the table.field information. Here is the SQL View. SELECT Employees.Department, Employees.Initials , Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked , Sum(ContractsAndHours.OutNewBusiness) AS NBOut , Sum(ContractsAndHours.OutRenewal) AS RnwlOut , Sum(ContractsAndHours.InNewBusiness) AS NBIn , Sum(ContractsAndHours.InRenewal) AS RnwlIn , [TotalHoursWorked]/8 AS EquivDays , CallsPerDay.[Out Calls] , CallsPerDay.[In Calls] , CallsPerDay.[Calls 3+] , ([Out Calls]/[EquivDays]) AS [Avg Out Calls] , ([In Calls]/[EquivDays]) AS [Avg In Calls] , ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls] FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID = Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID WHERE (((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate] And (ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate]))) GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls], CallsPerDay.[In Calls], CallsPerDay.[Calls 3+]; Thanks for taking a look. Lori "John Spencer" wrote: Did you create a form with the textbox controls and then enter the dates into the textbox controls as Ken suggested? The form must remain open to run the report. If you do as suggested you should not be prompted at all for the dates. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Ken, I am hoping you can help me since my issue is pretty much the same. I have two queries, both with date range parameters. When opening the report, with the subreport, I have to enter the parameters once for each query. I followed your instructions to the point of entering the parameters. Where/how do I enter the parameters. Do they go on the report or on the query? Each of my queries have different field dates. One query returns call records by date and the other query returns hours worked and contracts in/out by date. Any help would be appreciated. Thanks. Lori "KenSheridan via AccessMonster.com" wrote: You won't be able to do that in this case as the parameters relate to the queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 . . |
#8
|
|||
|
|||
subreport repeating parameters
I would very carefully check the spelling of the form name and control name on
the form and in the query to make sure you have not introduced a spelling error of some type. Beyond that I can see no reason for this to fail. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes one for start date and one for end date. There is also a button that is set to run the report. I open the form, enter the dates and click the button. I then get an Enter Parameter Value dialog box Start Date and End Date. I think I know where to look for the error, but am not sure what to look for. When running each query directly the first query is fine, but the second query is prompting for a Start and End Date. I believe I entered the parameters the same in each query and have compared them and do not see any differences, except the table.field information. Here is the SQL View. SELECT Employees.Department, Employees.Initials , Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked , Sum(ContractsAndHours.OutNewBusiness) AS NBOut , Sum(ContractsAndHours.OutRenewal) AS RnwlOut , Sum(ContractsAndHours.InNewBusiness) AS NBIn , Sum(ContractsAndHours.InRenewal) AS RnwlIn , [TotalHoursWorked]/8 AS EquivDays , CallsPerDay.[Out Calls] , CallsPerDay.[In Calls] , CallsPerDay.[Calls 3+] , ([Out Calls]/[EquivDays]) AS [Avg Out Calls] , ([In Calls]/[EquivDays]) AS [Avg In Calls] , ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls] FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID = Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID WHERE (((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate] And (ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate]))) GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls], CallsPerDay.[In Calls], CallsPerDay.[Calls 3+]; Thanks for taking a look. Lori "John Spencer" wrote: Did you create a form with the textbox controls and then enter the dates into the textbox controls as Ken suggested? The form must remain open to run the report. If you do as suggested you should not be prompted at all for the dates. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Ken, I am hoping you can help me since my issue is pretty much the same. I have two queries, both with date range parameters. When opening the report, with the subreport, I have to enter the parameters once for each query. I followed your instructions to the point of entering the parameters. Where/how do I enter the parameters. Do they go on the report or on the query? Each of my queries have different field dates. One query returns call records by date and the other query returns hours worked and contracts in/out by date. Any help would be appreciated. Thanks. Lori "KenSheridan via AccessMonster.com" wrote: You won't be able to do that in this case as the parameters relate to the queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 . . |
#9
|
|||
|
|||
subreport repeating parameters
I could not find a typo, but deleting the query and recreating it solved the
issue. It could be that I just wasn't seeing the discrepancy. Thanks. Lori "John Spencer" wrote: I would very carefully check the spelling of the form name and control name on the form and in the query to make sure you have not introduced a spelling error of some type. Beyond that I can see no reason for this to fail. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Yes, I created a form called frmWeeklyReport and added 2 unbound text boxes one for start date and one for end date. There is also a button that is set to run the report. I open the form, enter the dates and click the button. I then get an Enter Parameter Value dialog box Start Date and End Date. I think I know where to look for the error, but am not sure what to look for. When running each query directly the first query is fine, but the second query is prompting for a Start and End Date. I believe I entered the parameters the same in each query and have compared them and do not see any differences, except the table.field information. Here is the SQL View. SELECT Employees.Department, Employees.Initials , Sum(ContractsAndHours.DailyHours) AS TotalHoursWorked , Sum(ContractsAndHours.OutNewBusiness) AS NBOut , Sum(ContractsAndHours.OutRenewal) AS RnwlOut , Sum(ContractsAndHours.InNewBusiness) AS NBIn , Sum(ContractsAndHours.InRenewal) AS RnwlIn , [TotalHoursWorked]/8 AS EquivDays , CallsPerDay.[Out Calls] , CallsPerDay.[In Calls] , CallsPerDay.[Calls 3+] , ([Out Calls]/[EquivDays]) AS [Avg Out Calls] , ([In Calls]/[EquivDays]) AS [Avg In Calls] , ([Calls 3+]/[EquivDays]) AS [Avg 3+ Calls] FROM (ContractsAndHours INNER JOIN Employees ON ContractsAndHours.EmpID = Employees.EmpID) INNER JOIN CallsPerDay ON Employees.EmpID = CallsPerDay.EmpID WHERE (((ContractsAndHours.DateOfRecord)=[Forms]![frmWeeklyReport]![txtStartDate] And (ContractsAndHours.DateOfRecord)DateAdd("d",1,[Forms]![frmWeeklyReport]![txtEndDate]))) GROUP BY Employees.Department, Employees.Initials, CallsPerDay.[Out Calls], CallsPerDay.[In Calls], CallsPerDay.[Calls 3+]; Thanks for taking a look. Lori "John Spencer" wrote: Did you create a form with the textbox controls and then enter the dates into the textbox controls as Ken suggested? The form must remain open to run the report. If you do as suggested you should not be prompted at all for the dates. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County SSi308 wrote: Ken, I am hoping you can help me since my issue is pretty much the same. I have two queries, both with date range parameters. When opening the report, with the subreport, I have to enter the parameters once for each query. I followed your instructions to the point of entering the parameters. Where/how do I enter the parameters. Do they go on the report or on the query? Each of my queries have different field dates. One query returns call records by date and the other query returns hours worked and contracts in/out by date. Any help would be appreciated. Thanks. Lori "KenSheridan via AccessMonster.com" wrote: You won't be able to do that in this case as the parameters relate to the queries underlying the two subreports, not to the parents report's RecordSource, which in this case is Null, so you cannot filter the parent report by means of the OpenReport method's WhereCondition argument. You could pass values to hidden controls in the parent report when opening it and use these to restrict the subreports, but by far the simplest method is to reference controls on a dialogue form as the parameters. Lets say you want to restrict a report by date range, you'd create an unbound form, frmAuditDlg say, with two text box controls txtStartDate and txtEndDate, and a button to open your report ( or two buttons, one to preview, one to print). The queries underlying each subreport would then be along these lines: PARAMETERS Forms!frmAuditDlg!txtStartDate DATETIME, Forms!frmAuditDlg!txtEndDate DATETIME; SELECT * FROM [Audits] WHERE [AuditDate] = Forms!frmAuditDlg!txtStartDate AND [AuditDate] DATEADD("d",1,Forms!frmAuditDlg!txtStartDate); A couple of points to note: 1. Its particularly important to declare date/time parameters to avoid the risk of the value entered being misinterpreted as an arithmetical expression and giving the wrong results. 2. The method for defining the date range, as on or after the start date and before the day following the end date, is more reliable than a BETWEEN….AND operation as it caters for any AuditDate values inadvertently including a non- zero time of day, which can easily happen without you being aware of it if steps have not been taken in the table definition (i.e. a validation rule) to allow only dates with a zero time of day. When you want to open the report just open the dialogue form, enter the parameter values and click the button. In the button's Click event procedure just open the report unconditionally; the button wizard can create the code for you if you wish, but if you are reasonably familiar with VBA you can improve on this by including validation in the code, e.g. with the above example, to make sure both dates have been entered and the start date is not later than the end date. While the above is a perfectly adequate way of going about it you can if you wish include code in the report's Open event procedure to open the form if its not already open, do you can open the report directly rather than the form. There are a number of ways of doing this, but here's an example: Const FORMNOTOPEN = 2450 Dim frm As Form On Error Resume Next Set frm = Forms!frmAuditDlg If Err = FORMNOTOPEN Then Cancel = True DoCmd.OpenForm "frmAuditDlg" Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Note that with the above code, if you are opening the report with code elsewhere in the database you need to trap the error which occurs when the report's Open event's cancel argument's return value is set to True in the above code, e.g. Const REPORTCANCELLED = 2501 Dim frm As Form On Error Resume Next DoCmd.OpenReport "rptAudit", View:=acViewPreview If Err = REPORTCANCELLED Then 'anticipated error so ignore Else If Err 0 Then ' unknown error MsgBox Err.Description, vbExclamation, "Error" End If End If Ken Sheridan Stafford, England terri wrote: sorry...really not good at this. could you give me an example of this where clause? "auditor" and "auditdate" are the common fields in both queries. terri dazed and confused here.... [quoted text clipped - 13 lines] 2. leave the parameters in and point them to an unbound form that you use to collect the parameter values and pass them to the open event of the report. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201003/1 . . . |
Thread Tools | |
Display Modes | |
|
|