Calling a sub from another using date variables in ACCESS vba

Question

I am trying to invoke a sub from another sub in Access and keep getting a "Compiler Error ByRef argument mismatch. I understand that it says the variable I am passing does not match with the one specified in the called sub. Here is my code. I have the variables firstDayOfStartMonth and firstDayOfEndMonth defined as dates and am passing them to the second proc deleteCurrentData(startDate As Date, endDate As Date).

Any help would be greatly appreciated.

Sub loadDataFromExcel()

Dim monthName1, monthName2 As String

Dim startMonth, endMonth, curMonth   As Integer

Dim thisMonday, rptStartDate, rptEndDate, firstDayOfStartMonth, firstDayOfEndMonth As Date    

thisMonday = Date - Weekday(Date, vbMonday) + 1

rptStartDate = thisMonday - 14

rptEndDate = thisMonday - 10

firstDayOfStartMonth = DateSerial(Year(rptStartDate), Month(rptStartDate), 1)

firstDayOfEndMonth = DateSerial(Year(rptEndDate), Month(rptEndDate), 1)

Call deleteCurrentData(firstDayOfStartMonth, firstDayOfEndMonth)

End Sub

Private Sub deleteCurrentData(startDate As Date, endDate As Date)

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-01-31T20:08:17

I have the variables firstDayOfStartMonth and firstDayOfEndMonth defined as dates

Actually, it is not quite true!

When you're using this declaration

Dim thisMonday, rptStartDate, rptEndDate, firstDayOfStartMonth, firstDayOfEndMonth As Date

only firstDayOfEndMonth is type of Date, other values are Variant. Try to use this declarations instead

Dim thisMonday As Date, rptStartDate As Date, rptEndDate As Date, firstDayOfStartMonth As Date, firstDayOfEndMonth As Date

this will fix the problem.

P.S. the same thing with

Dim monthName1, monthName2 As String

and

Dim startMonth, endMonth, curMonth   As Integer

Change them to

Dim monthName1 As String, monthName2 As String

and

Dim startMonth, endMonth As Integer, curMonth As Integer

accordingly

Calling a sub from another using date variables in ACCESS vba

1 Answers