Run Stored Procedure From Access Vba

I keep getting an error of Conversion failed when converting date and/or time from character string. This is my vba I am using as well as my sql server stored procedure syntax.

Solution 1:

Consider using Format() to convert MS Access dates to string as concatenating quotes will not work. Also, use CONVERT() to convert varchar strings to date in SQL Server. The format YYYY-MM-DD is used to not be dependent on cultural settings with placement of months and days.

VBA

PrivateSub btnRunStoredProc_Click()
    Dim cmd As ADODB.Command, startdate AsString, enddate AsStringSet cmd = New ADODB.Command

    startdate = Format(Me.txtStartDate, "YYYY-MM-DD")
    enddate = Format(Me.txtEndDate, "YYYY-MM-DD")

    With cmd
       .ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
       .CommandType = adCmdStoredProc
       .CommandText = "runstoredproc"
       .Parameters.Append .CreateParameter("@startdate", adVarChar, adParamInput, 255, startdate)
       .Parameters.Append .CreateParameter("@enddate", adVarChar, adParamInput, 255, enddate)
       .Execute
    EndWithSet cmd = NothingEndSub

TSQL

ALTERProcedure [dbo].[runstoredproc]
(
       @startdatevarchar(100)
       ,@enddatevarchar(100) 
)
AsSELECT*FROM [helper]
WHERE hiredate BETWEENCONVERT(DATE, @startdate) ANDCONVERT(DATE, @enddate)

Solution 2:

Any reason your parameters can't be date/time data type? What is the data type of your hiredate column? This has always worked for me:

PrivateSub btnRunStoredProc_Click()
Dim cmd As ADODB.Command, startdate AsDate, enddate AsDateSet cmd = New ADODB.Command
    startdate = CVDate(Me.txtStartDate)
    enddate = CVDate(Me.txtEndDate)
    cmd.ActiveConnection = "Provider=sqloledb;Server=Server;Database=DB;Trusted_Connection=yes;"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "runstoredproc"
    cmd.Parameters.Append cmd.CreateParameter("@startdate", adDBTimeStamp, adParamInput, , startdate)
    cmd.Parameters.Append cmd.CreateParameter("@enddate", adDBTimeStamp, adParamInput, , enddate)
    cmd.Execute
EndSub


ALTER Procedure [dbo].[runstoredproc]
(
       @startdate datetime
       ,@enddate datetime 
)
AsSelect * from helper where hiredate between @startdate And @enddate