Calculate by month days served based on start date and end date into a report
I am creating an MS Access project that will determine the amount of time served by month and need it to return the results into a temporary table that will be used to populate a report. The criteria will be supplied in the initial form/report that will show the following:
Date_Committed as Date
Sentence_Length_Total_Days as Number
Good_Conduct_Time as Number
Prior_Time as Number
Total_Time_To_Serve as Number (Sen Length Days-(GCT+Prior)
Release_Date as Date
All of the above are known values and filled with either unbound fields from a form or a drop-down combo on the same form.
Ideally, I would like to show the following in another report or temporary table something like this:
Date Committed = 6/30/2020
Sentence Length = 365 Days
Good Conduct Time = 10 Days
Prior Time = 10 Days
Total Time To Serve = 365 Days
Release Date = 6/10/2021
Based on End Of Month
DateTotal Days ServedJul-202032Aug-202063The dataset should end on 6/10/2021 but is capable of increasing or decreasing based on sentence length.
Any suggestions would be greatly appreciated.