Scaling of decimal value resulted in data truncation

[John B]

Ok, I am getting the "Scaling of decimal value resulted in data truncation" which I know what it means but cannot figure out how to fix it. We upgraded are database from a 2000 version and the new version does not like the following query. I have isolated the problem to the crosstab row heading for Total. I cannot figure out why the newer version does not like this code or how to modify it to work. Any help would be appreciated. Pretty much anything I put in place of my current aggregate number doesn't error out including switching the row heading to other decimal point numbers. For instance, I can swap amount to the row heading instead of total and it will work fine.

<code>
PARAMETERS
[Forms]![frm_Family_Billing]![Date1] DateTime, [Forms]![frm_Family_Billing]![Date2] DateTime;
TRANSFORM
Sum(([WFee]+[WExp])) AS Amount
SELECT
dbo_Clients.Engrelated, BillingCodes.BillingDesc, Sum([Amount]) AS Total
FROM
((dbo_CltCustom
INNER JOIN
(dbo_WIP INNER JOIN dbo_Clients ON dbo_WIP.WCltID = dbo_Clients.ID) ON dbo_CltCustom.CltCustCltId = dbo_Clients.ID) INNER JOIN dbo_ServiceCodes ON dbo_WIP.WCodeID = dbo_ServiceCodes.ID) INNER JOIN BillingCodes ON dbo_ServiceCodes.ID = BillingCodes.ID
WHERE
(((BillingCodes.BillingDesc)<>"")
AND
((dbo_CltCustom.CltCustId)=5)
AND
((dbo_Clients.Engrelated)="JAN-015")
AND
((dbo_WIP.Wdate)>=[Forms]![frm_Family_Billing]![Date1]
And
(dbo_WIP.Wdate)<=[Forms]![frm_Family_Billing]![Date2])
AND
((dbo_WIP.Windicator)<>"D"))
GROUP BY
dbo_CltCustom.CltCustId, dbo_Clients.Engrelated, BillingCodes.BillingDesc
PIVOT
dbo_CltCustom.CltCustValue In (1,2,3,4,5);
</code>

 

[John B]

I found the answer to the problem on another forum. Apparently, when adding two decimal numbers together created this problem. The official response from Microsoft is to use the CDbl function to create double number. This solution worked when I switched the query in Access from an expression which was "too complex" to sum of the cdbl(wfee + Wexp).

I found the solution on dbforms.com.

John