• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Find nearest Tuesday In access

I have a field that has a enter entered in and I need some code that will look at that date and find the nearest tuesday and also tell if its a holiday. I already have a holiday table "tblholiday"

i'm clueless how to do this.
Cyprexx IT
Cyprexx IT
1 Solution
The following function calculates the nearest tuesday to a date passed to the function, and checks to see if it exists in a holiday table, adding a week if needed.

(See the inline comments)

Function GetNearestTuesday(dt As Date) As Date
    Dim intWeekDay As Integer
    Dim dtTemp As Date
    Select Case Weekday(dt)
        Case 3   ' Tuesday is day 3, so nearest Tuseday is the date passed to the function
            dtTemp = dt
        Case Is < 3  ' Date calculation for Sunday or Monday
            dtTemp = DateAdd("d", 3 - Weekday(dt), dt)
        Case Is > 3   ' Date calculation for other weekdays
            dtTemp = DateAdd("d", 10 - Weekday(dt), dt)
    End Select
    'If the calculated nearest tuesday is in the holiday table, add 7 days
    ' **** Note -- You need to adjust the following statement to use the correct table and field names ***
    If DCount("*", "YourTable", "Format([YourDateField],'yyyymmdd') = '" & Format(dtTemp, "yyyymmdd") & "'") > 0 Then
       GetNearestTuesday = DateAdd("d", 7, dtTemp)
    Else  ' Otherwise simply return the calculated date
       GetNearestTuesday = dtTemp
    End If
End Function

Open in new window

An example way to use the function would be:

msgbox (GetNearestTuesday(Date()))

That would give you the nearest tuesday to today's date.

Cyprexx ITAuthor Commented:

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now