Microsoft Access: returning a value from a function to a subroutine

Question

I am attempting to create a function that will adjust an entered date so as to adjust for weekends or holidays.  What have I done incorrectly, that the adjusted date does not pass from the function to the subroutine?

Private Sub DATE_ENTERED_AfterUpdate()

    Me!DATE_ENTERED = DATE_ADJUST(Me!DATE_ENTERED)

End Sub

Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date)

StartLoop:

    '/ if Saturday, add 2 days

        If Weekday(INITIAL_DATE) = 7 Then

            INITIAL_DATE = INITIAL_DATE + 2

        End If

    '/ if Sunday, add 1 day

        If Weekday(INITIAL_DATE) = 1 Then

            INITIAL_DATE = INITIAL_DATE + 1

        End If

    '/ if CAVC closing date, add 1 day and loop

        If Not (IsNull(DLookup("[CAVC closing date]", "[CAVC closing dates]", "[CAVC closing date] = #" & INITIAL_DATE & "#"))) Then

            INITIAL_DATE = INITIAL_DATE + 1

            GoTo StartLoop

        End If

End Function

Answer 1

After the End IF add:

DATE_ADJUST = INITIAL_DATE

You are not assigning the calculated date to the function, so there is nothing to return.

Also I would change the first line to:

Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date) AS Date

To make sure what is returned is a Date/Time datatype.

Answer 2

The "missing piece" was the line of code after the End If.  THANK YOU!

Answer 3

Actually, it is possible to use a function like a sub. for example:

Public Sub XMyDATE_ADJUST()

    Dim d As Date

    Dim e As Date

    e = Date   ' that is today

    d = MyDATE_ADJUST(e)   ' just add 1

    Debug.Print d, e

End Sub

'   output: 12:00:00 AM   12/13/2018