Calculated Field/ Decimal Precision Help.... MS Access 2k

[apellerin]

I am having an issue where I need to multiply a double value by a value with four decimal places within a calc field. However, it will only allow me to multiply by a whole number. "Invalid precision for a decimal" Suggestions?

If I reference a column in a table with the same number (37.8976) this works fine.

Example: Value: Avg(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE]="H",(([SumOfMRTYPE4_FA C]+[SumOfMRTYPE5_FA C])*37.8976),(IIf ([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE] Not Like "H",(([SumOfMRTYPE4]+[SumOfMRTYPE5])*37.8976),[SumOfAmt_REC]))))

When controlling the properties of the field in the query I can not specify decimal places, only General Number and Fixed data types with no precision control.

[ADezii]

I am having an issue where I need to multiply a double value by a value with four decimal places within a calc field. However, it will only allow me to multiply by a whole number. "Invalid precision for a decimal" Suggestions?

If I reference a column in a table with the same number (37.8976) this works fine.

Example: Value: Avg(IIf([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE]="H",(([SumOfMRTYPE4_FA C]+[SumOfMRTYPE5_FA C])*37.8976),(IIf ([CAP OR FFS]="CAP" And ([MRTYPE] Like "*a*") And [HOSP_OR_OFFICE] Not Like "H",(([SumOfMRTYPE4]+[SumOfMRTYPE5])*37.8976),[SumOfAmt_REC]))))

When controlling the properties of the field in the query I can not specify decimal places, only General Number and Fixed data types with no precision control.

You can use the Round() or FormatNumber() Functions to produce the desired number of Decimal Places. The following SQL Statements will multiply a Double Field ([MyDouble]) by 37.8976, and Round the results to 3 Decimal Places in a Calculated Field named [RetVal]. The Test Table used for this Demo is named tblTest, pretty ingenious, hey?

SELECT tblTest.MyDouble, Round([MyDouble]*37.8976,3) AS RetVal
FROM tblTest;

SELECT tblTest.MyDouble, FormatNumber([MyDouble]*37.8976,3) AS RetVal
FROM tblTest;

[mshmyob]

As always.

cheers,

[NeoPa]

Try :

Value: Avg(IIf([CAP OR FFS]='CAP' And ([MRTYPE] Like '*a*'),IIf([HOSP_OR_OFFICE]='H',CDbl([SumOfMRTYPE4_FAC])+CDbl([SumOfMRTYPE5_FAC]),CDbl([SumOfMRTYPE4])+CDbl([SumOfMRTYPE5]))*37.8976,[SumOfAmt_REC]))

I added in CDbl() to convert the non double values where necessary. This is because the defaults are to convert to integral types when unspecified.

[apellerin]

Thank you! I'll give these a shot.