SQL Round to 2 Decimal Points??

[daniela]

Hi, I have this query and I would like to have the avg display with only 2 decimal points. This is my SQL and I think I have to use this code but I'm not sure.

FORMAT(CountOfStudent Attended,'.00')

If I ad this after the SELECT statement my query will not work. What am I doing wrong?

Thanks!

~D


This works

SELECT [Attendance for Avg].CRN, Avg([Attendance for Avg].[CountOfStudent Attended])
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;

 

[gromit]

Hi Daniela -

The formatting is something separate from the SQL statement.

In the design view of the query, right click on the [CountofStudent Attended] field and select Properties.

Set the Format item to Fixed and then the Decimal Places item to 2.

hope that helps,

gromit

 

[llkhoutx]

When you actually display your data, where in a form or a report, use the following as the subject fields control source

=Round([FieldName],2)

or

set the control's

'Format' property to "Standard" without the quotation marks, and its
'Decimal Places' property to 2.

The second method is better in my mind.

Display query results similarily with

SELECT [Attendance for Avg].CRN, Round(Avg([Attendance for Avg].[CountOfStudent Attended]),2)
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;

 

[daniela]

Thanks

Gromit & llkhoutx

thanks, I selected the properties in the query and the form in which I am displaying this query and set the format to fixed and the decimal place to 2.

I love to make things harder than they are : )