Opening a report between two dates (1 Viewer)

Hello
I am creating a database to track overtime for my employees.
So I created 2 tables, one named EmployeesTable with fields (EmpID, EmpName) and one named OvertimeTable with fields (EntryID,EmpID,OvertimeDate,OvertimeHours).

I created a query to return the total overtime for each employee. The query is called OvertimeSumDateRangeQuery.
SQL statement for it is:

SELECT OvertimeTable.EmpID, Sum(OvertimeTable.OvertimeHours) AS SumOfOvertimeHours, EmpTable.EmpName
FROM EmpTable INNER JOIN OvertimeTable ON EmpTable.EmpID = OvertimeTable.EmpID
GROUP BY OvertimeTable.EmpID, EmpTable.EmpName;

I created a report based on the above query named OvertimeSumDateRangeReport

I created a form to allow the user to generate a report for the sum of overtime hours that occurr between two dates upon clicking a btnReportGenerate button. The date range is taken values entered in the unbound text boxes named txtFromDate and txtEndDate

The vba command that runs upon clicking the btnReportGenerate button is:
DoCmd.OpenReport "OvertimeSumDateRangeReport", acViewReport, "", "[OvertimeDate] Between #" & txtReportStartDate & "# And #" & txtReportEndDate & "#", acNormal

When button is clicked however the OvertimeDate field is not recognized and keeps popping up on the screen as input textbox.

I can see the reason behind that is that there is no record source for the OvertimeDate field in the report because this field does not exist in the underlying query for the report.

What needs to be done here? :banghead:

Add the field to your query.

Hi
thanks for your quick reply.

I am not sure how to add it since it's an aggregate query?
Can you please tell me how?

Sorry, didn't see that. You should unaggregate the query and instead aggregate it in the report.

So, remove the GROUP BY and the SUM around your fields in the query, add in OvertimeDate field. In your report, first make it work with the new query. Then make a grouping on EmpName and move all the fields to the group footer--nothing should be in the Details. Put the Sum() on the OvertimeHours field and your system should work.

I think it would work if you change your SQL to this...

SELECT OvertimeTable.EmpID, Sum(OvertimeTable.OvertimeHours) AS SumOfOvertimeHours, EmpTable.EmpName
FROM EmpTable INNER JOIN OvertimeTable ON EmpTable.EmpID = OvertimeTable.EmpID
WHERE OvertimeDate BETWEEN p0 AND p1
GROUP BY OvertimeTable.EmpID, EmpTable.EmpName;

Click to expand...

...and then change your code to this...
See what is going on there?
Mark

plog said:

Sorry, didn't see that. You should unaggregate the query and instead aggregate it in the report.

So, remove the GROUP BY and the SUM around your fields in the query, add in OvertimeDate field. In your report, first make it work with the new query. Then make a grouping on EmpName and move all the fields to the group footer--nothing should be in the Details. Put the Sum() on the OvertimeHours field and your system should work.

Click to expand...

Thank you Plog. That works. I never thought about putting an aggregate in the report. One thing though! now that there is nothing in the detail section of the report, I have extra space between the lines in the report. If I set the height of the detail section to Zero, I get an error message saying "Your query does not include the specified expression......"
It's not a big deal since I manged to make the height of the section to 0.1, but I wondered if there is a workaround that?

MarkK said:

I think it would work if you change your SQL to this...

...and then change your code to this...

Code:Copy to clipboard

DoCmd.SetParameter "p0", Me.txtReportStartDate
DoCmd.SetParameter "p1", Me.txtReportEndDate
DoCmd.OpenReport "OvertimeSumDateRangeReport", acViewReport

See what is going on there?
Mark

Click to expand...

Hi Mark. I see. I like this method. but unfortunately I am getting an empty report

Not sure if it's something to do with the way the dates are set to the parameters or ... no idea!

Thanks for your help

rabuayya said:

Hi Mark. I see. I like this method. but unfortunately I am getting an empty report

Not sure if it's something to do with the way the dates are set to the parameters or ... no idea!

Thanks for your help

Click to expand...

Mark I found it.
I just enclosed the text box values in "#" symbols in the code. I remembered that whenever passing dates to SQL statements they must be enclosed in #.
So my code now looks like this:
DoCmd.SetParameter "P0", "#" & Me.txtReportStartDate & "#"
DoCmd.SetParameter "P1", "#" & Me.txtReportEndDate & "#"
DoCmd.OpenReport "OvertimeSumDateRangeReport", acViewReport

Thank you very much for your help

Nice job, good troubleshooting skills.
Mark

I am only learning
Rami