Access Calculations

[Danny]

Greetings,
I have the following fields: AppStatus (with combo boxes New, Redeterm etc.), DateRecieved and DateDue. If New is selected from AppStatus drop down, I want DateDue field to be automatically 10 business days from the date application was received (DateDue= AppRecieved + 9). Can anyone tell me how to achieve this calculation?
I'm using Access 2K.

Thanks in advance.
DD

 

[KevinM]

Have a look at the DateAdd function in the Help files.

 

[The_Doc_Man]

The wrinkle is "10 BUSINESS days."

Unless you do business 5 or 7 days a week, you need to add different amounts depending on which day of the week is today.

If this is applicable, look up the help on the datepart function so you can determine the day of the week. You also need to express your rules very carefully here.

If, for example, Saturday and Sunday are NEVER valid working days, then you add 14 (not 10). Because if today is Monday, 10 working days from now is also a Monday 14 calendar days later. If today is Friday, then 10 working days from now is also a Friday 14 days later by the calendar.

If Saturday is considered a working day but Sunday is not, then you need to know whether today is Wednesday or Thursday. You either need to add 11 or 12 calendar days depending on whether 10 working days by that rule crosses 1 or 2 Sundays.


Of course, if you work 7 days per week, then it does not matter whether you cross a weekend or not. Adding 10 calendar days with DateAdd is no biggie at that point.