FormatNumber is rounding

# Thread: FormatNumber is rounding

1. Senior Member
Join Date
Dec 1969
Posts
2,031

## FormatNumber is rounding

I am using AVG in a SQL statement and then the FormatNumber Function when displaying the values. The results are rounding to the nearest whole number. How do you prevent rounding when calculating averages and using FormatNumber to display?<BR>Thanks,<BR>K

2. mb
Senior Member
Join Date
Dec 1969
Posts
142

## RE: FormatNumber is rounding

FormatNumber(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit [,UseParensForNegativeNumbers [,GroupDigits]]]])<BR><BR>so <BR>x = 100.023424<BR>x = formatnumber(x,3)<BR>gives you<BR>100.023<BR><BR>

3. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## Have you heard of FAQs??

check this out<BR><BR>http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=116

4. Senior Member
Join Date
Dec 1969
Posts
2,031

## RE: FormatNumber is rounding

So if the FormatNumber isn&#039;t causing the rounding do you think is the AVG in my SQL statement is causing it?<BR><BR>My SQL statement is: <BR>SQLQ1 = "SELECT AVG(Q1) FROM CourseEvaluations WHERE Instructor = &#039;"&Request("Trainer")&"&#039;"<BR>SQLQ1 = SQLQ1 & " AND Course_Code = &#039;"&Request("Course")&"&#039;"<BR>And then I display the number like this:<BR>&#060;%=FormatNumber(RSQ1(0),2)%&#062;<BR ><BR>

5. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## YES

it will give you the average DEPENDING on the datatype<BR><BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
2,031

## RE: YES

The datatype is tinyint (in SQL Server) so how do I get the "accurate" value. Not rounded to the nearest whole number?<BR>If the average is 4.23, I do not want a 4 returned.<BR>Thanks,<BR>K.<BR>

7. mb
Senior Member
Join Date
Dec 1969
Posts
142

## RE: FormatNumber is rounding

the return type of avg depends on the type of the field you&#039;re using. so you would have to cast the avg or convert it to a float if you wanted a non roudned number<BR><BR>select avg(cast(results as float)) as avgResult from results<BR><BR>this gives<BR>avgresult <BR>----------------------------------------------------- <BR>3.5743589743589745<BR><BR>while this <BR><BR>select avg(results) as avgresult from results<BR><BR>gives<BR><BR>avgresult <BR>----------- <BR>3<BR>

8. Senior Member
Join Date
Dec 1969
Posts
2,031

## Not to sound thick...

but how do I edit this to reflect your suggestion? Not sure if some of your references are "literal" and should be placed in the code as is. Thanks so much for your help.<BR><BR>SQLQ1 = "SELECT AVG(Q1) FROM CourseEvaluations WHERE Instructor = &#039;"&Request("Trainer")&"&#039;"<BR>SQLQ1 = SQLQ1 & " AND Course_Code = &#039;"&Request("Course")&"&#039;"<BR>

9. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

## CHANGE the datatype

if you can to double or real or float

10. Senior Member
Join Date
Dec 1969
Posts
2,031

## Wrong one Akhilesh...

I always read them first...and your link is not appropriate for what I am trying to do. I am not working with currency and therefore do not want a dollar sign, as the FormatCurrency gives. Also, the number of decimal places is not the issue. FormatNumber gives me two beautiful decimal places if I ask for two...but they are both zeros. We&#039;ve determined that the problem lies in my SQL statement in the average calculation, not in the Function used to display it. Thanks anyway.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•