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

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.
0
Cyprexx IT
1 Solution

Commented:
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.

``````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
Else  ' Otherwise simply return the calculated date
GetNearestTuesday = dtTemp
End If

End Function
``````

An example way to use the function would be:

msgbox (GetNearestTuesday(Date()))

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

0

Author Commented:
Wonderful
0

Featured Post

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