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:
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:
Last edited: