Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

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

Member
  
Join Date: May 2007
Posts: 55
#1: Dec 19 '07
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
Expert
  
Join Date: Jun 2007
Location: Derbyshire, UK
Posts: 347
#2: Jan 2 '08

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

Quote:

Originally Posted by 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(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
Expert
  
Join Date: Jun 2007
Location: Derbyshire, UK
Posts: 347
#3: Jan 3 '08

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

Quote:

Originally Posted by MikeTheBike

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
Reply

« previous question | next question »

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,327 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved