MS ACCESS Calculate due date depending on input

Question

HI All

I am working on an Incident management database, i require support to calculate the due date of the incident depending on the priority of the incident logged date

For Example:

Incident Prority is assigned as critical and logged today, so the due date for fix needs to be Today()+3 working days

If Incident Priority is High then it has to Today()+2 working days.

Can anyone help how to get this data done

Regards
Raghu

Answer 1

Priority and NumberOfDays are data so should be stored as values at column positions in rows in a table. Create a table with two columns like this:

Priorities
….Priority (PK)
….NumberOfDays

In a form's RecordSource query join the table to your current Incidents table on the Priority columns. You can then, in the Priority control's AfterUpdate event procedure, insert the number of working days from the Priorities table plus the IncidentDate into a text box control in the form bound to a column of DateTime data type in the Incidents table, or unbound with an expression as its ControlSource property if the due date is always to be determined by the incident date, and not capable of being modified by the user..

To compute the date take a look at Workdays.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file includes functions for adding working days either for a single country, or for multiple countries with different public holidays, e.g. the constituent counties of the UK and the Republic of Ireland.

Answer 2

You can use Switch and the function DateAddWorkdays from module DateWork at my library VBA.Date:

Priority = "high"  
Respite = Switch(Priority = "Normal", 5, Priority = "Critical Normal", 3, Priority = "High", 2, Priority = "Urgent", 0)  
  
' Respite -> 2  
' Date today is 2022-07-29  
  
DueDate = DateAddWorkdays(Respite, Date)   
' Duedate -> 2022-08-02