Trying to stop overflow error in function

[drbtodd1971]

I've written a function I want to return a total and I'm getting a runtime error 6 overflow when I access it. I know this is because a field is too big or of the wrong type but I can't seem to identify where it is overflowing.

Function code is as follows:

Public Function CalcPaymentsToDate(ClaimNumber As Integer) As Currency
Dim cmdCalcSettle As ADODB.Command
Dim prmClaimNumber As ADODB.Parameter
Dim prmCalcPaymentsToDate As ADODB.Parameter

Set cmdCalcSettle = New ADODB.Command
Set prmClaimNumber = cmdCalcSettle.CreateParameter("@ClaimNumber", adInteger, adParamInput)
Set prmCalcPaymentsToDate = cmdCalcSettle.CreateParameter("@Total", adCurrency, adParamOutput)

prmClaimNumber = ClaimNumber

With cmdCalcSettle
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "p_CalculateELPaymentsToDate"
.Parameters.Append prmClaimNumber
.Parameters.Append prmTotal
.Execute
End With

'Return PaymentTotal
CalcPaymentsToDate = cmdCalcSettle.Parameters("@Total").Value

' Close connection and clean up
cmdCalcSettle.ActiveConnection.Close
Set cmdCalcSettle = Nothing
End Function

-----------------------------------------------------------
Procedure code is as follows:

Alter Procedure p_CalculateELPaymentsToDate
(
@ClaimNumber Int
,@Total Money Output
)
As
set nocount on
SET @Total = (SELECT SUM(Amount)
FROM tblELSettlement
WHERE ClaimNumber = @ClaimNumber)
return

Any help would be much appreciated, I've tried changing the variables to longs but this doesn't help.

 

[JoeAtWork]

My guess is it is here:

Public Function CalcPaymentsToDate([COLOR=red]ClaimNumber As Integer[/color]) As Currency

In other words, the procedure that is calling CalcPaymentsToDate is passing a ClaimNumber that exceeds the maximum value for an Integer. Try changing ClaimNumber to a long.

Incidentally, an "Int" in SQL Server is roughly equivalent to a "Long" in VB.

 

[drbtodd1971]

Many thanks that has fixed the problem, I thought I had tried that but perhaps with other mistakes in the code. I also had one of the parameters named incorrectly which I quickly sorted.