I am developing an Access application using Access as the front end and SQL Server as the back end database.
On my interface, I have a text box that I named txtDateFrom with a format of Short Date.
I have another text box that I named txtDateTo with a format of Short Date.
I use the following code to execute a Stored Procedure and pass the 2 dates to the Stored Procedure as input paramters. Do you know where I went wrong becauses I am not retrieving the correct range of dates using my Stored Procedure. What value would I use as the size in the CreateParameter ?
Should I perform a conversion on the txtDateFrom and txtDateTo ?
I use a type of Datetime in the Stored Procedure for both input paramters as follows:
@DteFrom datetime and @DteTo datetime.
Set com = New ADODB.Command
.CommandType = adCmdStoredProc
.CommandText = "dbo.procUDLAAAAcctsMod"
.Parameters.Append .CreateParameter("DteFrom", adInteger, adParamInput, 8, txtDateFrom)
.Parameters.Append .CreateParameter("DteTo", adInteger, adParamInput, 8, txtDateTo)
.ActiveConnection = CurrentProject.Connection
Set rstQueryFS = .Execute
My stored procedure is as follows:
CREATE PROCEDURE dbo.procUDLAAAAcctsMod
@DteFrom datetime, @DteTo datetime
If Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblUDLAAAAccts' AND TYPE = 'U')
DROP TABLE dbo.tblUDLAAAAccts
SELECT Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number],
CASE WHEN C.TaxIDInd = "1" THEN " " ELSE UPPER(C.FirstName) END AS [First Name],
UPPER(C.MiddleInitial) AS [MI],
CASE WHEN C.TaxIDInd = "1" THEN C.FirstName ELSE UPPER(C.LastName) END AS [Last Name],
UPPER(C.SecondNameFirst) AS [2nd Name First],
UPPER(C.SecondNameMid) AS [2nd Name Mid], UPPER(C.SecondNameLast) AS [2nd Name Last],C.MasterCardNumber, C.DateLost As [Undeliverable Date],
DateDiff(d, C.DateLost, GetDate()) As [Counter (Run Date - Date Lost)], Right(C.OfficeNumber,3) As Branch
FROM dbo.tblCustomers1000 As C INNER JOIN dbo.tblProducts1000 As P ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber
WHERE C.RedFlag = 'N' AND P.DivPayOpt = '3'
ORDER BY C.Branch, C.MasterCardNumber