Misterious overflow

[nfk]

I have a report that's prints after click a button and the execution flow is as follows:

Button action:

 Call AppendTempPaymentsByYearByStaffID(CLng(Me.cboEmployerYear.Value))
stDocName = "rptEmployersEerMSTDebt"
If Me.chkAllYears.Value Then
 gstrReportSubHeader = "EerMST Debt for ALL Years"
 strSQL = "(([TotalEerMSTDue]-[TotalEerMSTPayment])< " & gcurDebtLL
Else
 gstrReportSubHeader = "EerMST Debt for Year = " & Me.cboEmployerYear.Value
 strSQL = "[Year]=" & Me.cboEmployerYear.Value
 strSQL = strSQL & " AND (([TotalEerMSTDue]-[TotalEerMSTPayment])< " & gcurDebtLL
End If
strSQL = strSQL & " OR ([TotalEerMSTDue]-[TotalEerMSTPayment])> " & gcurDebtUL & ")"

AppendTempPaymentsByYearByStaffID:

 Sub AppendTempPaymentsByYearByStaffID(lngYear As Long)
Dim cmdAppend As ADODB.Command
Set cmdAppend = New ADODB.Command
Dim prm As ADODB.Parameter
With cmdAppend
    Set .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "usp_AppendTempPaymentsByYearByStaffID"
    Set prm = .CreateParameter("@Year", adInteger, adParamInput, 4)
    prm.Value = lngYear
    .Parameters.Append prm
    Set prm = .CreateParameter("@StaffID", adInteger, adParamInput, 4)
    prm.Value = gUserID
    .Parameters.Append prm
    ' Run the SP usp_AppendTempPaymentsByYearByStaffID
    .Execute
End With
Set prm = Nothing
Set cmdAppend = Nothing
End Sub

And the stored procedure:

 ALTER PROCEDURE [dbo].[usp_AppendTempPaymentsByYearByStaffID]
(
@Year int,
@StaffID int
)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
     -- Insert statements for procedure here
DELETE FROM TblTempPayments WHERE [EMAIL="StaffID=@StaffID"]StaffID=@StaffID[/EMAIL]
 INSERT INTO TblTempPayments
                      (EntryNo, CRSNo, PIN, Year, DatePaid, PmtType, Amount, Reference, DebtPeriod, SubNo, StaffID, LoginName)
SELECT     EntryNo, CRSNo, PIN, Year, DatePaid, PmtType, Amount, Reference, DebtPeriod, SubNo, @StaffID AS StaffID, dbo.ExtractLoginName()
FROM         TblPayments
WHERE     (Year = @Year)

This is giving me:

The expression on click you entered as the event property setting produced the following error: Overflow.

I cant manage to track down what the problem might be... No freaking clue...

Any help would be appreciated.

 

[spikepl]

SO what expression did you enter for the button?

(BTW Listing snippets only is a bad bad habit)

 

[The_Doc_Man]

The only places I see where an overflow would be likely are:

The "INSERT INTO" can run into overflows based on data type mismatches, though I would have expected another error in that case

Your expression:

gstrReportSubHeader = "EerMST Debt for Year = " & Me.cboEmployerYear.Value

makes me question what values are stored/recovered from the cboEmployerYear item, which I infer to be a combo-box.

Other than those places, my best advice is to run this with suitable breakpoints in the code and see how far you get before it barfs.

 

[nfk]

Hi,

When I try with values:

gStaffID = 10
Year = 2014 it overflows,

when I go for Year = 2013 it works fine...

(?) what the hell... :banghead:

btw: it breaks at this point:

Sub AppendTempPaymentsByYearByStaffID(lngYear As Long)
Dim cmdAppend As ADODB.Command
Set cmdAppend = New ADODB.Command
Dim prm As ADODB.Parameter
With cmdAppend
Set .ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "usp_AppendTempPaymentsByYearByStaffID"
Set prm = .CreateParameter("@Year", adInteger, adParamInput, 4)
prm.Value = lngYear
.Parameters.Append prm
Set prm = .CreateParameter("@StaffID", adInteger, adParamInput, 4)
prm.Value = gUserID
.Parameters.Append prm
' Run the SP usp_AppendTempPaymentsByYearByStaffID
.Execute
End With
Set prm = Nothing
Set cmdAppend = Nothing
End Sub

 

[sonic8]

The Overflow error most likely occurs during your insert into on the SQL-Server-Table.

DELETE FROM TblTempPayments WHERE StaffID=@StaffID 
INSERT INTO TblTempPayments
           (EntryNo, CRSNo, PIN, Year, DatePaid, PmtType, Amount, Reference, DebtPeriod, SubNo, StaffID, LoginName) 
SELECT     EntryNo, CRSNo, PIN, Year, DatePaid, PmtType, Amount, Reference, DebtPeriod, SubNo, @StaffID AS StaffID, dbo.ExtractLoginName() 
FROM         TblPayments 
WHERE     (Year = @Year)

I suggest you run the statements in SQL Management Studio.
Replace @Variables with their actual values.

If this indeed causes an error, you should just run the select on its own and verify that the resulting values can actually be written to the columns of the target table.

 

[gemma-the-husky]

overflow might be a value of the wrong type - a long value being stored in an integer (2 byte) field, or a divide by zero error.

 

[The_Doc_Man]

This post should be sub-titled "Grasping at Straws"

If it breaks at the .Execute then there is no doubt that the INSERT INTO is somehow malformed or mal-executed. If your query works for gStaffID = 10 and Year = 2013, but fails for gStaffID = 10 and Year = 2014, then the year is being converted to something odd. I cannot say that it is entirely clear here, but I made a couple of guesses.

I had to assume that your problem was in date storage and that for some reason you were using a fixed-length field for storage of a decimal string. Further, I had to assume that you were converting to hours because it would be the only case that makes sense given what you described.

I did some math but could not find any obvious combination regarding the 2013/2014 transition, though if you were using units of hours in a fixed-decimal field, the 2014/2015 transition would overflow a special-case number.

So I did a little more math. If you were using a date after about 0800 on 27-Jan-2014, that would bring your date in MS Access to 1,000,000 hours elapsed time from the system reference date.

Is there a chance that something about your date is being converted to hours and that the date that fails is not just 2014, but is a date after 27-Jan-2014 - and that it is being stored in a decimal-string field limited to 6 characters? If so, you are being caught by a fixed-field overflow. This would be made even easier to understand if it happens that your back-end is one of those DB systems that takes fixed-length fields seriously.

If that ain't it, then you've got me stumped.

 

[gemma-the-husky]

I doubt if it is the year that is the problem.

What about the staffid?

I would view the data as a select query, and then sort up and down on each column. There will be some rogue data in there, I am sure. You only have a handful of columns.

 

[Minty]

Oh and Year is a system reserved word in SQL ? so it should have [ ] around it.