Hi,
Tjhis is my first post (nervous ).
I am Report in Access that runs total of employee's hours for a range of date. The below is a copy of the SQL statement from that report and it works fine with those dates. But when I tried to call the report from another Form (using VBA), it keeps asking for Parameter Values.
SELECT tbl_Work_Logs.Employee_Number, Sum(tbl_Work_Logs.Lab_Office_hrs) AS [lab\office], Sum(tbl_Work_Logs.Well_site_hrs) AS Wellsite, Sum(tbl_Work_Logs.OverTime_hrs) AS OverTime, Sum(tbl_Work_Logs.Sick_hrs) AS [Sick Leave], Sum(tbl_Work_Logs.Training_hrs) AS Trainings, Sum(tbl_Work_Logs.Stat_holiday_hrs) AS [Stat Holidays], Sum(tbl_Work_Logs.Vacation_hrs) AS Vacations, Sum(tbl_Work_Logs.Shift_Dift_Aft_hrs) AS [Shift Diff Aft], Sum(tbl_Work_Logs.Shift_Dift_Night_hrs) AS Shift_Diff_Night, Sum(tbl_Work_Logs.H2S_Diff_Hrs) AS [H2S Diff], Sum(tbl_Work_Logs.Field_Pay_Hrs) AS [Field Pay], Sum(tbl_Work_Logs.Total_Hrs_DBCalc) AS Total, Employee.First_Name, Employee.[Middle Name], Employee.Last_Name, Employee.[Employee Group], Employee.Job_Title, Employee.[Access Level], Employee.Stratum_Location_ID, Max(tbl_Work_Logs.Date_Worked) AS Pay_End_Date, Min(tbl_Work_Logs.Date_Worked) AS Pay_Start_Date FROM Employee INNER JOIN tbl_Work_Logs ON Employee.Employee_Number = tbl_Work_Logs.Employee_Number WHERE (((tbl_Work_Logs.Date_Worked) Between #3/15/2020# And #3/28/2020#)) GROUP BY tbl_Work_Logs.Employee_Number, Employee.First_Name, Employee.[Middle Name], Employee.Last_Name, Employee.[Employee Group], Employee.Job_Title, Employee.[Access Level], Employee.Stratum_Location_ID ORDER BY tbl_Work_Logs.Employee_Number;
Here is my code from the other Form: DoCmd.OpenReport "rpt_employees_TS_summary_by_Group", acViewPreview, , "[employee].[employee group]= '" & Me.cbo_emp_group.Column(0) & "' and [tbl_work_logs].[date_worked] between #" & Me.tbx_Start_date & "#" & " and #" & Me.tbx_EndDate & "#", acDialog
But the pop up little window keeps coming up to ask for Parameter Values.
The work around this is to assign the dates (From the report SQL Design) to the Form's textboxes of dates. But I dont want to do that because when I am fixing the report and try to preview it on its own, it will have error as it cannot find the values on Forms (unless it is running). T
hanks in advance for your help. Andy
Tjhis is my first post (nervous ).
I am Report in Access that runs total of employee's hours for a range of date. The below is a copy of the SQL statement from that report and it works fine with those dates. But when I tried to call the report from another Form (using VBA), it keeps asking for Parameter Values.
SELECT tbl_Work_Logs.Employee_Number, Sum(tbl_Work_Logs.Lab_Office_hrs) AS [lab\office], Sum(tbl_Work_Logs.Well_site_hrs) AS Wellsite, Sum(tbl_Work_Logs.OverTime_hrs) AS OverTime, Sum(tbl_Work_Logs.Sick_hrs) AS [Sick Leave], Sum(tbl_Work_Logs.Training_hrs) AS Trainings, Sum(tbl_Work_Logs.Stat_holiday_hrs) AS [Stat Holidays], Sum(tbl_Work_Logs.Vacation_hrs) AS Vacations, Sum(tbl_Work_Logs.Shift_Dift_Aft_hrs) AS [Shift Diff Aft], Sum(tbl_Work_Logs.Shift_Dift_Night_hrs) AS Shift_Diff_Night, Sum(tbl_Work_Logs.H2S_Diff_Hrs) AS [H2S Diff], Sum(tbl_Work_Logs.Field_Pay_Hrs) AS [Field Pay], Sum(tbl_Work_Logs.Total_Hrs_DBCalc) AS Total, Employee.First_Name, Employee.[Middle Name], Employee.Last_Name, Employee.[Employee Group], Employee.Job_Title, Employee.[Access Level], Employee.Stratum_Location_ID, Max(tbl_Work_Logs.Date_Worked) AS Pay_End_Date, Min(tbl_Work_Logs.Date_Worked) AS Pay_Start_Date FROM Employee INNER JOIN tbl_Work_Logs ON Employee.Employee_Number = tbl_Work_Logs.Employee_Number WHERE (((tbl_Work_Logs.Date_Worked) Between #3/15/2020# And #3/28/2020#)) GROUP BY tbl_Work_Logs.Employee_Number, Employee.First_Name, Employee.[Middle Name], Employee.Last_Name, Employee.[Employee Group], Employee.Job_Title, Employee.[Access Level], Employee.Stratum_Location_ID ORDER BY tbl_Work_Logs.Employee_Number;
Here is my code from the other Form: DoCmd.OpenReport "rpt_employees_TS_summary_by_Group", acViewPreview, , "[employee].[employee group]= '" & Me.cbo_emp_group.Column(0) & "' and [tbl_work_logs].[date_worked] between #" & Me.tbx_Start_date & "#" & " and #" & Me.tbx_EndDate & "#", acDialog
But the pop up little window keeps coming up to ask for Parameter Values.
The work around this is to assign the dates (From the report SQL Design) to the Form's textboxes of dates. But I dont want to do that because when I am fixing the report and try to preview it on its own, it will have error as it cannot find the values on Forms (unless it is running). T
hanks in advance for your help. Andy