Can you use VBA to pass parameter as criteria for Report with Grouping/Aggregation in MS Access? (1 Viewer)

TechGuy

New member
Local time
Today, 08:33
Joined
Apr 21, 2020
Messages
9
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:33
Joined
Aug 30, 2003
Messages
36,118
The parameter prompt is Access telling you it can't find something. What does the prompt ask for?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF! What do the parameter prompts look like?
 

TechGuy

New member
Local time
Today, 08:33
Joined
Apr 21, 2020
Messages
9
oh sorry. it is prompting for Date_Worked field. see attachment
 

Attachments

  • prompt4parameter_value.JPG
    prompt4parameter_value.JPG
    16.5 KB · Views: 511

theDBguy

I’m here to help
Staff member
Local time
Today, 07:33
Joined
Oct 29, 2018
Messages
21,357
oh sorry. it is prompting for Date_Worked field. see attachment
Okay, thanks. If your report is bound to the query you posted above, then I don't see a column in that query for date_worked. So, you can't filter the report using that column.
 

plog

Banishment Pending
Local time
Today, 09:33
Joined
May 11, 2011
Messages
11,611
You've got bigger problems than this issue. You need to properly structure you data. That process is called normalization:


You should not be storing data in field names: Lab_Office_hrs, Well_site_hrs, OverTime_hrs, Sick_hrs, etc. Everything you've used as a prefix to "_hrs" (e.g. LabOffice, WellSite, Overtime, etc.) should be data in a field, not the names of fields. You need an HoursType field where you store that for each record, not a column for each type.

You have a field called [Employee].[Employee Group] which is the correct way to do this. You did not create a field named after each group that an employee could be a part of. Instead you put that into the [Employee Group] field. That is what you need to do with all your hours.
 

TechGuy

New member
Local time
Today, 08:33
Joined
Apr 21, 2020
Messages
9
Okay, thanks. If your report is bound to the query you posted above, then I don't see a column in that query for date_worked. So, you can't filter the report using that column.
Yes. That is true. Thanks for your response.
 

TechGuy

New member
Local time
Today, 08:33
Joined
Apr 21, 2020
Messages
9
You've got bigger problems than this issue. You need to properly structure you data. That process is called normalization:


You should not be storing data in field names: Lab_Office_hrs, Well_site_hrs, OverTime_hrs, Sick_hrs, etc. Everything you've used as a prefix to "_hrs" (e.g. LabOffice, WellSite, Overtime, etc.) should be data in a field, not the names of fields. You need an HoursType field where you store that for each record, not a column for each type.

You have a field called [Employee].[Employee Group] which is the correct way to do this. You did not create a field named after each group that an employee could be a part of. Instead you put that into the [Employee Group] field. That is what you need to do with all your hours.
yes. good catch. I am testing out a small timesheet database. I will definitely work on normalization when I get to build it.
Cheers.
 

Users who are viewing this thread

Top Bottom