Return a Date from an Access VBA Function to a Subroutine
Question details
Return a calculated date from an Access VBA function to the subroutine that calls it.

- Product
- Microsoft Access VBA
- Device & OS
- not provided
- Scenario
- A subroutine calls a function that adjusts a date and needs to use the result.
- Observed behavior
- The function does not return the calculated date when the result is not assigned to the function name.
Open the Access database and locate the function and calling subroutine in the VBA editor. Identify where the date adjustment finishes so you can assign the final value before the function ends.
Assign the calculated date to the Access VBA function name
Use this approach when the function calculates a date but the calling subroutine does not receive it.
In Access, press Alt+F11 to open the VBA editor, then locate DATE_ADJUST in the module that contains it.
Change the function declaration to `Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date) As Date` so VBA treats its return value as a Date.
After the existing date-adjustment logic, including its closing `End If` if applicable, add `DATE_ADJUST = INITIAL_DATE` before `End Function`. If the result is stored in a different variable, assign that variable instead.
In the calling subroutine, assign the result with `d = DATE_ADJUST(e)`. For example, declare `d` and `e` as Date, set `e = Date`, and use `Debug.Print d, e` to inspect the values in the Immediate window. Open that window with Ctrl+G in the VBA editor.

Work with Office documents and spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and Microsoft Office format support for easier migration. It does not fix Access VBA functions or replace an Access database.

Frequently Asked Questions
Why does my Access VBA function not return the calculated date?
In VBA, a function returns the value assigned to its name. Add an assignment such as `DATE_ADJUST = INITIAL_DATE` after the calculation and before `End Function`.
How do I declare that the function returns a date?
Add `As Date` after the parameter list, as in `Public Function DATE_ADJUST(ByRef INITIAL_DATE As Date) As Date`.
How can I check the returned value?
In the calling subroutine, use `d = DATE_ADJUST(e)` followed by `Debug.Print d`. Press Ctrl+G in the VBA editor to view the output in the Immediate window.
Will changing the date parameter inside the function change the original variable?
It can when the argument is passed ByRef, as in this example. Change the parameter declaration to `ByVal INITIAL_DATE As Date` if you want the function to modify a copy instead.




