Arithmetic Overload

[Carly]

I am using Microsoft Access Project and I have got the following stored procedure which creates a table:

ALTER PROCEDURE dbo.[Qry D1 GroupedFail&Success]
(@EnterDate1 datetime,
@EnterDate2 datetime)
AS 
SELECT		CONVERT(char(10), wcs_entry_date_time, 103) AS Date, RTRIM(wcs_zone) AS Zone, 
            	COUNT(CASE WHEN Type = 'Fail' THEN wcs_po_number END) AS Fail,
            	COUNT(CASE WHEN Type = 'Success' THEN wcs_po_number END) AS Success
INTO 		[tbl D1 GroupedFail&Success]
FROM		dbo.[Qry D UnionFail&Success] ()
WHERE  	(wcs_entry_date_time BETWEEN @EnterDate1 AND @EnterDate2)
GROUP BY 	CONVERT(char(10), wcs_entry_date_time, 103), RTRIM(wcs_zone), MONTH(wcs_entry_date_time), DAY(wcs_entry_date_time), YEAR(wcs_entry_date_time)
ORDER BY 	MONTH(wcs_entry_date_time), DAY(wcs_entry_date_time), YEAR(wcs_entry_date_time)

The table it creates has the following data types:

Date = Char(10)
Zone = varchar(12)
Fail = int(4)
Success = int(4)

I then need to do some calculations on this table with the following stored procedure:

ALTER PROCEDURE dbo.[Qry D2 %OfFailedPutAwaysByZone]
AS SELECT     Date, LTRIM(Zone) AS Zone, Fail, Success, ROUND(CASE WHEN (Fail + Success) = 0 THEN 0 ELSE Fail / (Fail + Success) * 100 END,1) AS [% Fail]
FROM         dbo.[tbl D1 GroupedFail&Success]

The problem I have got is that the report just shows 0 for the last column due to the data type of the table. I had a function to run these stored procedures as follows:

Public Function cmdDFailedPutAways()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As ADODB.Parameter, param2 As ADODB.Parameter
Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
cmd.CommandText = "dbo.[Qry D1 GroupedFail&Success]"
cmd.CommandType = adCmdStoredProc
Set param1 = cmd.CreateParameter("@EnterDate1", adDBDate, adParamInput)
    cmd.Parameters.Append param1
    param1.Value = Null
    param1.Value = [Forms]![Frontpage]![TxtDate1]
Set param2 = cmd.CreateParameter("@EnterDate2", adDBDate, adParamInput)
    cmd.Parameters.Append param2
    param2.Value = Null
    param2.Value = [Forms]![Frontpage]![TxtDate2]
DoCmd.RunSQL ("DROP TABLE [tbl D1 GroupedFail&Success]")
cmd.Execute
DoCmd.RunSQL ("ALTER TABLE [tbl D1 GroupedFail&Success] ALTER COLUMN [Fail] Decimal(2)")
DoCmd.RunSQL ("ALTER TABLE [tbl D1 GroupedFail&Success] ALTER COLUMN [Success] Decimal(2)")
DoCmd.OpenForm "Frm D FailedPutAways"
End Function

which used to work but now when I have gone back to it is gives me an error when it gets to the 1st ALTER TABLE line, the error is as follows:

Run-time error '8115':
Arithmetic overflow error converting numeric to data type numeric.

Could anyone tell me what I am doing wrong?

Any help would be greatfully received

Regards

Carly