Time & Attendance Access - Need to summarize data as of a specific date

Happy to help.

Good luck with your project.

Hi Guys - I am on the final report for this item and need some help.

So - I have my unbound form in which I enter my start/end dates. I currently have a button on the form that runs the query for this. However, I also have 2 additional queries that need to run with the same end date to get the final report. I am trying to figure out a way to enter the date only once, run the 3 queries and output to a report in a single macro. I will also configure same to output directly to excel. Ultimately I would like to put 1 button for each (report and excel) on my original unbound form and be done with it.

Hoping that can be done.

I don't think you need info below in red. I have created a macro that runs everything but I still have to enter the dates for each.

Option Compare Database

'------------------------------------------------------------
' Macro1
'
'------------------------------------------------------------
Function Macro1()
On Error GoTo Macro1_Err

DoCmd.OpenQuery "StartEnd-CONV Query", acViewNormal, acEdit  
DoCmd.OpenQuery "Specific Date-CONV Query", acViewNormal, acEdit  
DoCmd.OpenQuery "Final Date Query - CONV", acViewNormal, acEdit  
DoCmd.OpenReport "Final Date Report - CONV", acViewReport, "", "", acNormal  
DoCmd.Close acQuery, "Final Date Query - CONV"  
DoCmd.Close acQuery, "Specific Date-CONV Query"  
DoCmd.Close acQuery, "StartEnd-CONV Query"  

Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

Currently I:

Enter Date into form and run Query1

SELECT Attendance.[Employee File Number and Name], Sum(Attendance.[Vac Hrs]) AS PTOTaken, Sum(Attendance.[Management Adjustment]) AS Adjustments
FROM Attendance
WHERE (((Attendance.[Date Absent]) Between [Forms]![StartEnd-CONV]![txtStart] And [Forms]![StartEnd-CONV]![txtEnd]))
GROUP BY Attendance.[Employee File Number and Name]
ORDER BY Attendance.[Employee File Number and Name];

Run Query2 (where I am prompted to enter the end date again)

SELECT [Employee Name and Information].[Employee File Number], [Employee Name and Information].[Company Code], [Employee Name and Information].[Original Date of Hire], [Employee Name and Information].[BBF Accrued Hrs], [Employee Name and Information].[Separation Date], [Employee Name and Information].[Rehire Date], [Employee Name and Information].[Vacation Time Override], [Employee Name and Information].[Last Day of the Year], [Enter Date] AS [Date], Year([Date]) AS CurrentYr, IIf([Rehire Date]>1,[Rehire Date],[Original Date of Hire]) AS [Hire Date], Year([Hire Date]) AS HireYr, [CurrentYr]-[HireYr] AS [#YrsEmployed], IIf([#yrsEmployed]>20,160,IIf([#yrsEmployed]>10,120,IIf([#yrsEmployed]>=1,80,IIf([#yrsEmployed]<1,0)))) AS Entitlement, DateDiff('d',[Hire Date],[Last Day of the Year])+1 AS DaysHiretoYE, [DaysHiretoYE]/365 AS [%ofYear], [%ofYear]*80 AS EarnedHrs, IIf([EarnedHrs]<80,[EarnedHrs],[Entitlement]) AS FinalEarnedHrs, IIf([Vacation Time Override]>0,[Vacation Time Override],[FinalEarnedHrs]) AS FinalEntitlement, DateSerial((2022),1,1) AS 1stDayofYr, IIf([Last Day of the Year]>0,[Hire Date],[1stDayofYr]) AS AccrualStart, DateDiff('d',[AccrualStart],[Date])+1 AS DaysWorked, [DaysWorked]/365 AS [%Year], [FinalEntitlement]*[%Year] AS EarnedHrsAvail
FROM [Employee Name and Information]
WHERE ((([Employee Name and Information].[Company Code])="XXX"));

Run my Final Report which in turn runs Query3 (where I am prompted to enter the end date again)

SELECT [StartEnd-CONV Query].[Employee File Number and Name], [StartEnd-CONV Query].PTOTaken, [StartEnd-CONV Query].Adjustments, [Specific Date-CONV Query].[Company Code], [Specific Date-CONV Query].[BBF Accrued Hrs], [Specific Date-CONV Query].[Separation Date], [Specific Date-CONV Query].EarnedHrsAvail, [Specific Date-CONV Query].Date, [Specific Date-CONV Query].[Rehire Date]
FROM [StartEnd-CONV Query] INNER JOIN [Specific Date-CONV Query] ON [StartEnd-CONV Query].[Employee File Number and Name] = [Specific Date-CONV Query].[Employee File Number]
WHERE ((([Specific Date-CONV Query].[Separation Date])>=[Date] Or ([Specific Date-CONV Query].[Separation Date]) Is Null)) OR ((([Specific Date-CONV Query].[Rehire Date])>=[Separation Date]))
ORDER BY [StartEnd-CONV Query].[Employee File Number and Name];

Thanks!!!
Leslie