Public Function With Variables Referencing Table Date Fields, Access 2010 (1 Viewer)

I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
I am a VBA novice, and need a help with the syntax.

Here is the code:

Option Compare Database
Option Explicit

Public Function getCurrentRepDates() As Date

Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
Dim dtEndDate As Date
dtEndDate = DLookup("EndDate", "tblCurrentRepDates")
Dim dtCurrentRepDates As Date
dtCurrentRepDates = Between dtBegDate And dtEndDate

getCurrentRepDates = gdtCurrentRepDates

End Function


I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code?

Appreciate your help!

Yeah, the between function is used wrong, the return value from the between function is logic, (true/false).
The below tested if a date is between the dates in the fields BegDate & EndDate.
But to help you further we need exactly to know what you want to provide.
By the way you've have a typo in your function:

getCurrentRepDates = gdtCurrentRepDates

Click to expand...

Thanks for you quick reply. I am setting up the date range to use as a parameter for several queries that would show only current month's reports. So far, I have it hard-coded and have to change the parameters every month in each query. I want to use a variable for that purpose, so that an admin changes it in one place only - through the bound form.

I still do not understand why I should put some date in the Select statement, as I will be using the function reference as a parameter in a query. I need an equivalent of "Between...and " statement in VBA.

Thanks for catching the typo.

krasniusha said:

Thanks for you quick reply. I am setting up the date range to use as a parameter for several queries that would show only current month's reports. So far, I have it hard-coded and have to change the parameters every month in each query. I want to use a variable for that purpose, so that an admin changes it in one place only - through the bound form.

Click to expand...

Show 1 SQL-string then it is easier to see what you have now.

krasniusha said:

I still do not understand why I should put some date in the Select statement, as I will be using the function reference as a parameter in a query. I need an equivalent of "Between...and " statement in VBA.

Click to expand...

My sample was only to show how to use Between in a query.

A function can't return a range of dates as a single date variable. You could return an array or a recordset with multiple dates.

If you want to return the first and last dates as separate date variables then you would have to pass the two date parameters ByRef and modify them in the function.

Another alternative is to return a string with the BETWEEN and AND included ready to concatenate into an SQL query.

Thanks, Galaxiom.
I made two UDFs for beginning and end dates and then included them in the Between parameter. Worked great!