Remove Prompt while running report

[knn9413]

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.

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.Task

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.

strWhere = "WorkDate BETWEEN #" & txtMgrRptStartDate & "# AND #" & txtMgrRptEndDate & "#"
DoCmd.OpenReport "rptTotalProjectHours", acViewPreview, "qryTotalProjectHours", strWhere, acWindowNormal

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.

• 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..

 

[CJ_London]

try

DoCmd.OpenReport "rptTotalProjectHours", acViewPreview, , strWhere, acWindowNormal

assuming your report is using qryTotalProjectHours as it's recordsource, you do not use the filtername option unless that query includes a where clause.

see this link for more detail regarding their usage

https://msdn.microsoft.com/en-us/library/bb243764(v=office.12).aspx

 

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/forums/showthread.php?p=1421753&posted=1#post1421753

 

[knn9413]

try

assuming your report is using qryTotalProjectHours as it's recordsource, you do not use the filtername option unless that query includes a where clause.

see this link for more detail regarding their usage

https://msdn.microsoft.com/en-us/library/bb243764(v=office.12).aspx

I tried that as well. No luck.

 

[JHB]

You haven't included the field "WorkDate" in your query so it is unknown in the report.
Then you also have to format the dates to the US format, database is attached.

 

[knn9413]

That result is incorrect though. You will see 2 different rows for the same Project and Task since you included the Workdate. What I wanted was the first 2 rows combined into one.. That is why I am questioning if access can even do that. I know Oracle, DB2 support that feature.

Year End Tax Project / Analysis / 4 - This is correct instead of the 2 entries below

Year End Tax Project / Analysis / 2
Year End Tax Project / Analysis / 2

 

[Cronk]

Try using date formatting in query strings, viz

strWhere = "WorkDate BETWEEN #" & format(txtMgrRptStartDate,"mm/dd/yyyy") & "# AND #" & format(txtMgrRptEndDate,"mm/dd/yyyy") & "#"

 

[knn9413]

Try using date formatting in query strings, viz

strWhere = "WorkDate BETWEEN #" & format(txtMgrRptStartDate,"mm/dd/yyyy") & "# AND #" & format(txtMgrRptEndDate,"mm/dd/yyyy") & "#"

Doesn't work. I somehow think that it is mandatory to include the column name in the SELECT statement. Might be an Access limitation for all I know. The moment you include the column name in the query, it works fine.

 

[Cronk]

What is stored in the variable strWhere on execution?

You might also check what the report's record source is in the OnOpen event.

 

[pbaldy]

I think it's already been pointed out, but I'll reiterate that you can't do what you're trying to do. You're telling the report to filter on a field that it has no knowledge of, since it isn't included in its source. This is a case where you're going to need to move the filtering to the query I think.

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)
WHERE WorkDate BETWEEN Forms!FormName.txtMgrRptStartDate AND Forms!FormName.txtMgrRptEndDate
GROUP BY TasksEntries.Project, TasksEntries.Task

 

[knn9413]

I think it's already been pointed out, but I'll reiterate that you can't do what you're trying to do. You're telling the report to filter on a field that it has no knowledge of, since it isn't included in its source. This is a case where you're going to need to move the filtering to the query I think.

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)
WHERE WorkDate BETWEEN Forms!FormName.txtMgrRptStartDate AND Forms!FormName.txtMgrRptEndDate
GROUP BY TasksEntries.Project, TasksEntries.Task

Thanks Paul. That worked. Wonder why I did not think of that before :banghead:

 

[pbaldy]

No problem.