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

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • eskelies
    New Member
    • May 2007
    • 55
    #1

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

    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
    Tags: None
    • MikeTheBike
      Recognized Expert Contributor
      • Jun 2007
      • 640
      #2
      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(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.
      Code:
      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

        Comment

        • MikeTheBike
          Recognized Expert Contributor
          • Jun 2007
          • 640
          #3
          Originally posted by MikeTheBike
          Hi

          Much as I like endless loops(?), I think something like this should do what you have described.
          Code:
          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
          Code:
          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

            Comment

            Previous template Next
            Working...