I have a date field on a form that I would like to specify a default value. The value I would like to use is the last business day. So, if today is Tuesday, 10/09/2012, I would like today's default value to be 10/08/2012 <-- The last business day before today.
In addition to that, I have another need for the same type of field, only using Tuesday thru Saturday as business days. So if today is Tuesday, 10/09/2012, the default value for this new field would be 10/06/2012 <-- The last "business" day for this particular week.
On the basis that the first requirement is business day are Monday to Friday (5 days) and the second is Tuesday to Faturday (5 day) then you couid use this function to return the previouse business day before to-day for either requirement
Function PreviousBusDay(ByVal iFirstDay As Integer) As Date
Dim iDay As Integer
Dim iOffset As Integer
iDay = Weekday(Date, iFirstDay)
If iDay = 1 Then
iOffset = 3
ElseIf iDay <= 6 Then
iOffset = 1
iOffset = 2
PreviousBusDay = DateAdd("d", -iOffset, Date)
for W/C Monday use
and W/C Tuesday
Or any W/C 5 day period using the relevant day constant!
To use this as a Default for a text box, use the Form Load event like this
Private Sub Form_Load()
Me.Text1.DefaultValue = "PreviousBusDay(" & vbMonday & ")"
using the appropriate constant
Or just Type
in the Default property dialogue for the text box, but you will have to enter the value of the constant (2=vbMonday, 3=vbTuesday)