6th Business Day of the Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's the issue...

I have a database that contains records that can be modified by managers
(lets say they were attributed volume that wasn't theirs). The data can be
changed until the 6th business day of the following month, so if a record was
added on Sept 1st, it would have to be updated before the 6th business day of
October.

Right now, we manually change the value in a table to reference this date.
If the current date is greater than the entry date of a record, a message box
pops up stating a change can no longer be made.

I am looking for code to be able to find the 6th business day of the current
month, and use that to check records. Just to let you know how I will use the
data, a text box on a form that searches for records will house the date.

Any help is appreciated.
 
Here's the issue...

I have a database that contains records that can be modified by managers
(lets say they were attributed volume that wasn't theirs). The data can be
changed until the 6th business day of the following month, so if a record was
added on Sept 1st, it would have to be updated before the 6th business day of
October.

Right now, we manually change the value in a table to reference this date.
If the current date is greater than the entry date of a record, a message box
pops up stating a change can no longer be made.

I am looking for code to be able to find the 6th business day of the current
month, and use that to check records. Just to let you know how I will use the
data, a text box on a form that searches for records will house the date.

Any help is appreciated.
Click to expand...

use this:
http://www.datastrat.com/Code/GetBusinessDay.txt
 
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function
 
Thanks for the help. I got the day by modifying the code a little to get what
I needed. Wouldn't have been able to create this myself.

Take Care...

Klatuu said:
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
'D Hargis
'OriginalDate = First Day to calculate number of working days from
'DaysToAdd = Number of Working Days to add to OriginalDate
'Returns the date that is the last working day for the number of days
'To look back, pass a negative number of days
'If 0 is entered, the current date is returned

Dim intDayCount As Integer
Dim dtmReturnDate As Date
Dim intAdd As Integer
'Determine whether to add or subtract
Select Case DaysToAdd
Case Is >= 1
intAdd = 1
Case Is = 0
AddWorkDays = OriginalDate
Exit Function
Case Else
intAdd = -1
End Select

intDayCount = 0
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + intAdd
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", intAdd, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

--
Dave Hargis, Microsoft Access MVP


PiB311 said:
Here's the issue...

I have a database that contains records that can be modified by managers
(lets say they were attributed volume that wasn't theirs). The data can be
changed until the 6th business day of the following month, so if a record was
added on Sept 1st, it would have to be updated before the 6th business day of
October.

Right now, we manually change the value in a table to reference this date.
If the current date is greater than the entry date of a record, a message box
pops up stating a change can no longer be made.

I am looking for code to be able to find the 6th business day of the current
month, and use that to check records. Just to let you know how I will use the
data, a text box on a form that searches for records will house the date.

Any help is appreciated.
Click to expand...
Click to expand...
 
Back
Top