By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

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

P: 55
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(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 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("[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
Dec 19 '07 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
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(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 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("[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.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
  2. 'OriginalDate = First Day to calculate number of working days from
  3. 'DaysToAdd = Number of Working Days to add to OriginalDate
  4. 'Returns the date that is the last working day for the number of days
  5. 'To look back, pass a negative number of days
  6. 'If 0 is entered, the current date is returned
  7.  
  8. Dim i As Integer
  9.  
  10. AddWorkDays = OriginalDate
  11. For i = 1 To DaysToAdd Step Sgn(DaysToAdd)
  12.     If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
  13.         If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
  14.             AddWorkDays = OriginalDate + i
  15.         End If
  16.     End If
  17. Next i
  18. 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
Jan 2 '08 #2

Expert 100+
P: 634
Hi

Much as I like endless loops(?), I think something like this should do what you have described.
Expand|Select|Wrap|Line Numbers
  1. Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
  2. 'OriginalDate = First Day to calculate number of working days from
  3. 'DaysToAdd = Number of Working Days to add to OriginalDate
  4. 'Returns the date that is the last working day for the number of days
  5. 'To look back, pass a negative number of days
  6. 'If 0 is entered, the current date is returned
  7.  
  8. Dim i As Integer
  9.  
  10. AddWorkDays = OriginalDate
  11. For i = 1 To DaysToAdd Step Sgn(DaysToAdd)
  12.     If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
  13.         If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
  14.             AddWorkDays = OriginalDate + i
  15.         End If
  16.     End If
  17. Next i
  18. 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
Expand|Select|Wrap|Line Numbers
  1. Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As Date
  2. 'OriginalDate = First Day to calculate number of working days from
  3. 'DaysToAdd = Number of Working Days to add to OriginalDate
  4. 'Returns the date that is the last working day for the number of days
  5. 'To look back, pass a negative number of days
  6. 'If 0 is entered, the current date is returned
  7.  
  8. Dim i As Integer
  9.  
  10. AddWorkDays = OriginalDate
  11. If DaysToAdd = 0 Then Exit Function
  12. For i = 0 To DaysToAdd Step Sgn(DaysToAdd)
  13.     If Weekday(OriginalDate + i, vbMonday) <= 5 Then 'It is a weekday
  14.         If IsNull(DLookup("[Date]", "Holidays", "[Date] = #" & Format(OriginalDate + i, "mm/dd/yy") & "#")) Then
  15.             AddWorkDays = OriginalDate + i
  16.         End If
  17.     End If
  18. Next i
  19. End Function
Line 11 added and line 12 moded.

MTB
Jan 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.