convert a number value to decimal

[ciarra41]

I need to convert numbers or currency like below to two decimal places. It's not working in the properties either.


I'm getting this:
27.3333333333333
55.0833333333333
null
91.7875
0
8

But I need this:
27.33
55.08
0.00
91.78
0.00
8.00

SELECT Master.rep, Sum([correct total]/12) AS TTl
FROM Master
GROUP BY Master.rep

 

[dhookom]

If you want the value expressed with two decimal places, you should use the Round() function. Try something like:

SELECT Master.rep, Round(Sum([correct total]/12),2) AS TTl
FROM Master
GROUP BY Master.rep

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

You seem to be truncating ... not rounding.

91.7875 would round to 91.7[red]9[/red] but you want 91.7[red]8[/red].

Try

SELECT Master.rep, 
       Int(Sum([correct total]/12)*100.0)/100.0 AS TTl
FROM Master
GROUP BY Master.rep

 

[dhookom]

A review of your desired values suggests converting null to 0 in addition to the truncating. You can use Nz([Correct Total],0) to change nulls to 0.

Duane
Hook'D on Access
MS Access MVP