I have the following stored procedure in a SQLServer db. The sp takes 3 parameters, one text and two dates.
CREATE PROCEDURE sp_DonationType_TotalAmount
@myDonationType varchar, @myBeginDate datetime, @myEndDate datetime
select Sum(a.Amount) as SumAmount
from tblDonationAllocations a inner join tblDonations d on a.DonationID = d.DonationID
where (a.DonationType = @myDonationType) and (d.DonationDate between @myBeginDate and @myEndDate)
I have an Access Project (.adp) front-end to the db and use VBA code to run the sp and create a recordset. The code that passes the parameters using a command object is:
Dim myBeginDate, myEndDate as Date
Dim myDonationType as String
Dim mySumAmount as Currency
myCmd.Parameters("@myBeginDate") = myBeginDate
myCmd.Parameters("@myEndDate") = myEndDate
myCmd.Parameters("@myDonationType") = myDonationType
Set myRs = myCmd.Execute
mySumAmount = !SumAmount
The value passed as myDonationType is a string, eg "ADMIN" and the dates passed are entered via a Form like this: 3/12/02 (for December 3, 2002). This is UK format.
Can you please tell me what I have done wrong as I always get a data type error for the string parameter. Also the dates don't seem to work correctly; how do I fix that?