Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: translate to vb code

    can someone help me
    how can i do this in VB code to use it in an ms access form

    1. every 3rd wednesday of the month
    2. every 1st tuesday and 3rd tuesday of the month

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Found this after a quick google search.

    Code:
    Public Function NthWeekday(Position, DayIndex As Long, TargetMonth As Long, Optional TargetYear As Long)
    
        ' Function by Patrick Matthews
    
        ' Returns any arbitrary weekday (the "Nth" weekday) of a given month
        ' Position is the weekday's position in the month.  Must be a number 1-5, or the letter L (last)
        ' DayIndex is weekday: 1=Sunday, 2=Monday, ..., 7=Saturday
        ' TargetMonth is the month the date is in: 1=Jan, 2=Feb, ..., 12=Dec
        ' If TargetYear is omitted, year for current system date/time is used
       
        ' This function as written supports Excel.  To support Access, replace instances of
        ' CVErr(xlErrValue) with Null.  To use with other VBA-supported applications or with VB,
        ' substitute a similar value
       
        Dim FirstDate As Date
    
        ' Validate DayIndex
        If DayIndex < 1 Or DayIndex > 7 Then
            NthWeekday = CVErr(xlErrValue)
            Exit Function
        End If
       
        If TargetYear = 0 Then TargetYear = Year(Now)
    
        Select Case Position
           
            'Validate Position
            Case 1, 2, 3, 4, 5, "L", "l"
               
                ' Determine date for first of month
                FirstDate = DateSerial(TargetYear, TargetMonth, 1)
               
                ' Find first instance of our targeted weekday in the month
                If Weekday(FirstDate, vbSunday) < DayIndex Then
                    FirstDate = FirstDate + (DayIndex - Weekday(FirstDate, vbSunday))
                ElseIf Weekday(FirstDate, vbSunday) > DayIndex Then
                    FirstDate = FirstDate + (DayIndex + 7 - Weekday(FirstDate, vbSunday))
                End If
               
                ' Find the Nth instance.  If Position is not numeric, then it must be "L" for last.
                ' In that case, loop to find last instance of the month (could be the 4th or the 5th)
                If IsNumeric(Position) Then
                    NthWeekday = FirstDate + (Position - 1) * 7
                    If Month(NthWeekday) <> Month(FirstDate) Then NthWeekday = CVErr(xlErrValue)
                Else
                    NthWeekday = FirstDate
                    Do Until Month(NthWeekday) <> Month(NthWeekday + 7)
                        NthWeekday = NthWeekday + 7
                    Loop
                End If
           
            ' This only comes into play if the user supplied an invalid Position argument
            Case Else
                NthWeekday = CVErr(xlErrValue)
        End Select
       
    End Function
    Untested, no idea if it works or not.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •