Find nearest Tuesday In access

Posted on 2011-10-31
Last Modified: 2013-11-27

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.
Question by:Cyprexx IT
    LVL 61

    Accepted 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.


    Author Closing Comment

    by:Cyprexx IT

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    C# primary key 9 46
    Sql code problem 6 14
    message box in access 4 23
    SQL syntax error in VBA 11 21
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now