I posted this within the Reports as well, but since this has a little bit of VBA decided to ask this here as well.
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period. A given Task can we worked on by multiple individuals and hence I need to find a way to aggregate the hours spent.
I accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
When the report is invoked, I get a box where it says
"Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.
I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have attached the cut down version of the database that I am working on.
When you do that you will see the prompt come up. Enter any date and you will see the report. The report generated uses the results from the query and does not filter on the date selected in the form. This is what I have been struggling to fix since yesterday but have reached nowhere..
I have a report that is created from the following query (qryTotalProjectHours). What I am trying to do is get the total hours spent on Tasks within a given time period. A given Task can we worked on by multiple individuals and hence I need to find a way to aggregate the hours spent.
Code:
SELECT TasksEntries.Project, TasksEntries.Task, Sum(TimeTracker.WorkHours) AS TotalHours
FROM TasksEntries INNER JOIN TimeTracker
ON (TasksEntries.EmployeeId = TimeTracker.EmployeeId) AND (TasksEntries.TaskID = TimeTracker.TaskId)
GROUP BY TasksEntries.Project, TasksEntries.TaskI accept the start and end dates in a form and pass it like shown below. WorkDate is a column in the TimeTracker table and is not present in any other table.
Code:
strWhere = "WorkDate BETWEEN #" & txtMgrRptStartDate & "# AND #" & txtMgrRptEndDate & "#"
DoCmd.OpenReport "rptTotalProjectHours", acViewPreview, "qryTotalProjectHours", strWhere, acWindowNormalWhen the report is invoked, I get a box where it says
"Enter parameter value" for Workdate..
Is there anyway I can get rid of the prompt? I never thought you needed the column name in the SELECT statement to be able to run this.
I should add the I tried the query with the WorkDate hardcoded in there and it worked fine and returned the correct results
I have attached the cut down version of the database that I am working on.
- Open up the frmManagerReport Form
- Leave the Employee drop down empty
- Enter the start and end dates ( I have used 4/1/2015 and 4/30/2015)
- Select the 3rd option "Generate Tasks by Total Hours"
- click on Run
When you do that you will see the prompt come up. Enter any date and you will see the report. The report generated uses the results from the query and does not filter on the date selected in the form. This is what I have been struggling to fix since yesterday but have reached nowhere..
Remove Prompt while running report
