Unclear how to perform this action in Access. Help needed if possible.

[eskelies]

Hello all. I am very new to this and I don't know if VBA is the better route to go.

First and foremost I am running Access 2003.

I have a table in my Access database. This table is made up of three fields. The first being Date. The second being Day of the Week (ie. Sunday, Monday, etc.) and the third being holiday/weekend. Under the column holiday/weekend I have inputted a "Y" if it is a weekend or holiday; Else, I have a null value.

In another table, I have my daily earnings for Monday - Friday. On Friday, my daily earnings will carry forward through the weekend/holiday. For instance, I earned $100 dollars on Friday, which means I will earn $100 on Saturday, Sunday or even a holiday.

I do not know how to write the code to project for the weekend.
Below is what I believe the code for determining the weekend/holiday.

Any help would be greatly appreciated as I am extremely confused.

Public Function AddWorkDays(Ori ginalDate As Date, DaysToAdd As Integer) As
Date
'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(Origina lDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup( "[Date]", "Holidays", _
"[Date] = #" & 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

[MikeTheBike]

Hello all. I am very new to this and I don't know if VBA is the better route to go.

First and foremost I am running Access 2003.

I have a table in my Access database. This table is made up of three fields. The first being Date. The second being Day of the Week (ie. Sunday, Monday, etc.) and the third being holiday/weekend. Under the column holiday/weekend I have inputted a "Y" if it is a weekend or holiday; Else, I have a null value.

In another table, I have my daily earnings for Monday - Friday. On Friday, my daily earnings will carry forward through the weekend/holiday. For instance, I earned $100 dollars on Friday, which means I will earn $100 on Saturday, Sunday or even a holiday.

I do not know how to write the code to project for the weekend.
Below is what I believe the code for determining the weekend/holiday.

Any help would be greatly appreciated as I am extremely confused.

Public Function AddWorkDays(Ori ginalDate As Date, DaysToAdd As Integer) As
Date
'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(Origina lDate, vbMonday) <= 5 Then 'It is a weekday
If IsNull(DLookup( "[Date]", "Holidays", _
"[Date] = #" & 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

Hi

Much as I like endless loops(?), I think something like this should do what you have described.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
'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 i As Integer
 
AddWorkDays = OriginalDate
For i = 1 To DaysToAdd Step Sgn(DaysToAdd)
    If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
        If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
            AddWorkDays = OriginalDate + i
        End If
    End If
Next i
End Function

I would suggest using a yes/no data type for the 'Holiday' field. Also, please change the field name for 'Date' (this is a keyword/function) to something else, if you can, as this will probably give you problems at some time.

MTB

[MikeTheBike]

Hi

Much as I like endless loops(?), I think something like this should do what you have described.

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
'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 i As Integer
 
AddWorkDays = OriginalDate
For i = 1 To DaysToAdd Step Sgn(DaysToAdd)
    If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
        If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
            AddWorkDays = OriginalDate + i
        End If
    End If
Next i
End Function

I would suggest using a yes/no data type for the 'Holiday' field. Also, please change the field name for 'Date' (this is a keyword/function) to something else, if you can, as this will probably give you problems at some time.

MTB

Hi
On overnight refection there is a logical hole in previous code (when OrigionalDate is not a valid workday and DaysToAdd =0), so two mods

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
'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 i As Integer
 
AddWorkDays = OriginalDate
If DaysToAdd = 0 Then Exit Function
For i = 0 To DaysToAdd Step Sgn(DaysToAdd)
    If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
        If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
            AddWorkDays = OriginalDate + i
        End If
    End If
Next i
End Function

Line 11 added and line 12 moded.

MTB