Access Crashes on Decimal Fields

[Guest]

Does anyone know what the limit on Access's handling of Decimal Fields is ?
While developing some dynamic SQL fields (with precision 19 and scale 10)
caused Access to crash repeatedly. (I guess there is some poor person @
Microsoft trying to decipher the crash responses I caused.) When I changed
the field to to integers, the problem was resolved. I really need to be
working to around 8 decimal places.......what data type suits? (Floating
point is not suitable due to inaccurate calculation...)

The SQL where the problem manifested itself is below:


SELECT D.DomainName as Domain , dbo.GetLevel(MD.RecordId) as Level ,
SUM(CASE WHEN MD.MeasureDate = '2005-10-01' THEN MD.Weighting ELSE 0 END)
AS [Oct-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-11-01' THEN MD.Weighting ELSE 0 END)
AS [Nov-2005],
SUM(CASE WHEN MD.MeasureDate = '2005-12-01' THEN MD.Weighting ELSE 0 END)
AS [Dec-2005],
SUM(CASE WHEN MD.MeasureDate = '2006-01-01' THEN MD.Weighting ELSE 0 END)
AS [Jan-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-02-01' THEN MD.Weighting ELSE 0 END)
AS [Feb-2006],
SUM(CASE WHEN MD.MeasureDate = '2006-03-01' THEN MD.Weighting ELSE 0 END)
AS [Mar-2006]
FROM dbo.MeasureDetails MD INNER JOIN dbo.Measures M ON MD.measureId =
M.measureId
INNER Join dbo.Domains D ON M.domainId = D.domainId
WHERE M.TimeFrame = 'Monthly '
AND MD.MeasureDate BETWEEN '2005-10-01' AND '2006-04-01'
AND dbo.GetLevel(MD.RecordId) NOT IN (' ')
GROUP BY D.DomainName, dbo.GetLevel(MD.RecordId)
ORDER BY D.DomainName, dbo.GetLevel(MD.RecordId)

 

[Sylvain Lafontaine]

Maybe a regional problem, see
http://support.microsoft.com/default.aspx?scid=KB;EN-US;275108 .

In your case, you could try converting these values to strings.