calculate change in totals based on Date and PreviousDate

[oneleaf5]

Hi Folks,

I'm getting an "overflow" message when running this function. Does anyone know what I'm doing wrong?

I'm a bit of a newbie so don't laugh at my code/logic. The user enters a date in a parameter query which gets passed to the function. The function is supposed to find the next previous date to the date entered, and grab the "total msps" from the VTD_MSP_Comp field for both the DateEntered and the DatePrevious. Then subtract these totals from each other and return the difference.

Am I close? Any help is appreciated.

Thanks!

oneleaf

--------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Function calcChange(dtReportDate As Variant) As Single

' this function calculates the change in value
' of TotalMSPs for records equal to NextToLastDate
' from TotalMSPs for records equal to LastDate

On Error GoTo FunctionError

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
strSQL = "Select RCCID, VTD_MSP_Comp, ReportDate from tblReport_RCC_MSP;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

Dim dtNextToLastDate As Variant
dtNextToLastDate = DMax("[ReportDate]", "[tblReport_RCC_MSP]", "[ReportDate] < #" & dtReportDate & "#")

Dim intMSPsDateLast As Integer
Dim intMSPsDateNextToLast As Integer
Dim intChange As Integer

With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF

If dtReportDate = rs!ReportDate Then
intMSPsDateLast = rs!VTD_MSP_Comp
Else
intMSPsDateLast = 2
End If

If dtNextToLastDate = rs!ReportDate Then
intMSPsDateNextToLast = rs!VTD_MSP_Comp
Else
intMSPsDateNextToLast = 1
End If

intChange = Nz(intMSPsDateLast) - Nz(intMSPsDateNextToLast)
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing

calcChange = intChange

'If Len(Nz(intChange)) > 0 Then
' calcChange = intChange
' Else
' calcChange = Null
' End If

FunctionEnd:
Exit Function

FunctionError:
MsgBox Err.Description
Resume FunctionEnd

End Function

 

[PHV]

I'm getting an "overflow" message
Which line of code raises this error ?

Tip: temporarly comment out the On Error GoTo instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886