SQL Round to 2 Decimal Points??

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;


Post your answer or comment

comments powered by Disqus
Hi everyone,

Wonder if someone can point me in the right direction.
I am trying to round down a number to 2 decimal places at all times.

Say a price of a loaf of bread is 1.69 and it is on a sale at half price, access then says it is 0.85 which it isn't, it's 0.84.
I am trying to keep the currency out of this but am using single/general number.

Would be grateful if someone could look at it, it's been driving me mad!

Thanks
Stu

This is probably an easy question, but I can't figure out how to get a table to show numbers in 2 decimals points when they are inputted with 2-5 decimal points. I changed the DATA TYPE to Number and DECIMAL PLACES to 2, but when I do that, it just rounds it to a whole number. Please help, thanks!

I have this code:

MaxCPC = Bid2 / 7

But I want MaxCPC to be stored to 2 decimal places. If I click in the field, quite often I get something like 2.3256 but I only want 2.32.

How?

Does anyone know how to force a listbox to display an additional level of accuaracy in a listbox which displays a value based on an expression in a querry? My querry displays the value to 4 decimal point but my listbox only diplays the value to 2 decimal point.
Thanks!

I am computing a value based on a %. If I round and the answer is say 100.125 the result is 100.12 instead of 100.13.

If I add 0.005 I will get the correct value for this, but if I get an answer of say 100.3848 and I round it after adding the .005, my answer is wrong.

My problem would be solved if I could test the number of digits after teh decimal place.

using the right(Cinput, 1) function to test the last decimal, it returns either the 2nd, 3rd or 4th digits depending on how many decimal places there are.

Any help would be appreciated

Can someone show me how to re-write this expression to limit the result to two decimal places? [D], [P], [R] and [C] are just a few monetary columns I am combining for a total owed. When I change the format on the table (this is an update query), it rounds up to the nearest whole number and leaves the right side of the decimal point at two zeros

Collection Percent: -Sum(Nz([P],0)/((Nz([D],0)+Nz([R],0))+Nz([C]+0)))

Thanks!

I would like to force a field to be 2 decimal places in the SQL view of a query as I am creating a UNION.

If is there anyway of doing this? I have tried CONVERT() & FORMAT() but I can't seem to get this to work.

Any help would be greatfully received.

Kind Regards

Carly

hi,
I have dividing one int field by another and the results varies from 0 to 9999.999999999999

When I add a SUM(newnumber) into a group field in reports I get the error :
This expression is typed incorrectly, or it is too complex to be evaluated.

I think it might have something to do with the large decimal place.

from my query can I convert this number to 9999.99 at most ?
ie, only 2 decimal places..
Access should then beable to add the values,, correct ?


thank you for any help..

I'm using the internal round() function in A2003, but it is not working. My number I am rounding to 2 decimal places is 10.325. This should round to 10.33, but Access is rounding it to 10.32.

I also found an external function on the internet which has the same error. Here is the function:

	Code:
	Function RoundIt(ByVal Number As Variant, ByVal Decimals As Integer) As Variant

    If Decimals >= 0 Then
        RoundIt = Int(Number * 10 ^ Decimals + 0.5) / 10 ^ Decimals
    Else
        MsgBox "Invalid decimal places."
        RoundIt = Number
    End If

End Function

How can I get Access to round properly? This is a very serious problem to be a penny off. When you are talking about a million calculations, those million pennies really add up.

EDIT:
The Number that is passed to this function is actually of type Single. Here is my broken down code so I could trace what is going on:

	Code:
	Public Function Roundcr(ByVal Number As Variant, ByVal Decimals As Integer) As Variant
Dim v As Variant
' At this point, Number=10.324999999999 but displays as 10.325. Thus begins my problem.
If Decimals >= 0 Then
    v = (Number * (10 ^ Decimals)) ' v=1032.4999999
    v = v + 0.5 ' v=1032.9999999
    v = Int(v) ' v=1032
    v = v / (10 ^ Decimals) ' v=10.32 which is NOT CORRECT.
    Roundcr = v
Else
    MsgBox "Global::Roundcr: Invalid decimal places."
    Roundcr = Number
End If

End Function



All

I've searched these fora and know about export specifications but I can't figure out how to make them work in my instances. The story so far:

I have a module which imports a file called DBImport.csv from the Desktop into a temporary table called DataTemp, after getting the user to confirm that:Field names appear on the first line The first field is called Member Which of four unique identifiers are used under Member Based on this information, the module then:Adds a new column called Serial, at the end of the table Populates Serial using one of four queries determined by 3 above Exports the records where Serial is NULL to DBErrors.csv on the Desktop Drops the column Member from DataTemp Exports the records where Serial is NOT NULL to DBExport.csv on the Desktop Drops DataTemp from the database The challenge is that the export always rounds to 2 decimal places. I could get around this using an Export Specification, except that they aren't dynamic. This code copes with whatever data is thrown at it, so long as the criteria are met. However, rounding is unacceptable.

As an aside, if anyone can tell me how to move Serial to be the first column, that'd be appreciated too.

Does anyone know how to specify how many decimal places are displayed for a decimal in a field on a form.

I am currently using the round function

So Round(x / y, 2) This displays x / y rounded to 2 decimal places.

Muy problem is that if x divided by y is an integer (i.e. no decimal places needed), I still want it to show 00 behind the decimal point, but it doesn't.

Anyone know how to fix this.

Thanks
Tracy

I have some texboxes (read from a datasheet) that have some problems with the decimal format. The field "Density" is shown in a form and a report. In the form I selected the format as "Fixed" and Decimal = 3, and got the number I wanted. In the report I did the same, selected the format as "Fixed" and Decimal = 3, and for some reason it's rounding to 2 decimals. I switched to 4 decimals and it's still 2.

The only difference between the form and the report is that the form is bound and the report is from a query.

I'm pretty sure I can get the correct value by changing the density field in the SQL to "round(table.density, 3) as density", but that smacks of duct tape.

Any ideas?

I have a list bound to a query - one of the fields is a currency field.
In the query, I can show more than 2 decimals in the result, but when used in the list, it rounds off the result to 2 decimal places.

What can I check?

thanks

I would like the users to enter values of day 2.5 however it always rounds up to 3 i have checked the propeties of the field in the table and i have set it to 2 decimal places. I have also checked the property of the field on the form and done the same. There must be something i am missing.

Thanks for lookin gand your help.

Made an access data base for our company policies and procedures.

Fields are:

Section
Section Title
Policy
Explanation
Related Documents - (hyperlink field)

Running a query on the table for the report the delivers the policy.

the Section Field is Rich Text - this is the only way I can figure out how to display decimal point divisions like, 1.1.0, or 6.5.3 (for relevant sections).

Query won't return anything when I set a parameter on Section Feild and I suspect it's because of the Rich Text issue. I've tried smart tag but no avail.

I'm open to suggestions here.

Help????


And the next thing I want to do is have an index that gives me Section and Section Title with a go to. How best to achieve that???

I have a calculated field in a query which I want to format to Currency to 0 decimal points, either in the query or in the form which the query serves. I have an idea that it is something like Format$ but have not been able to guess how to apply it. I would like to know exactly where to put what, in relation to the fieldname and calculation, e.g.

FieldName:[Calculation])

Thank you for any help.

Owl

I have a line graph chart based on a cross tab query in which the Data Table is displayed.

The problem is in the Data Table the values are displayed to 7 decimal points, I would like to limit the display to 4 decimal points like in the underlying cross tab query.

I can't seem to make this work, any ideas?

I am using merge in Word to import a calculation from an Access query. The query calculation displays to 2 decimal points by setting format and decimal places - no problem here. In the Word merge field, the calculation is displayed to about 15 decimal places. I only want two decimal places. Any ideas?

Thanks
John

I have an access database which I have imported all information from an excel spreadsheet. The values in Access are the results of complex formulas and have resulted in values with many numbers after the decimal point (ie 1.593874522 etc). I have formated these values to 2 decimal points (ie 1.59) but when formatting a number, it just changes how that number appears.

Is there a way to remove the extra decimal points entirely? I understand that this will change the value entirely, but I want the actual value to reflect the number format.

Is there any way to do this?

Thank you very much for your help

In a query I am working on, I am summarizing financial data. All my numbers are in dollar amounts, so there are two decimal places. However, many of the numbers that I'm getting from the query results have many more places. For instance, I'm getting numbers like 5.0931703299284E-11 when it should be 0 and 5598.23999999878 when it should be 5539.24. Clearly, there are some rounding errors going on here. How do I get my results to round to 2 decimal places so that they display correctly? I tried right clicking on the field and setting decimal places to 2 under field properties, but that didn't change anything.

I have a select query where a field produces values like -5.72246220980126. What can I do to persuade the number to be -5.73 i.e. to 2 decimal places.

I have a field with numbers in it. I specified in the table that the field holds up to 2 decimal places. I did the same in queries that contain that field and columns with calculations that utilize those numbers. However, I still see even up to four or five decimal places used in my query and table fields. How can I get my 2 decimal places to show?

A2003 on Win XP

Fieldname: Cost
Fieldtype: currency (this may be my problem)
Decimals: 3

I have a table with a field called "cost". In the table def the Decimals property says "3". I have a combo box on a form which is used as a Search box, so it is not bound to anything. But the RowSource property of the combo box uses the Cost field. When I run the form and drop down the combo box, the Cost column rounds to 2 decimals.

How can I get the Cost column to show all 3 decimals as specified in the Table def?

Thanks.

p.s. This is a dollar amount for buying larger quantities of an item, but I might have to change it to a Single field type to show 3 decimals.

Here is the formula that I inserted within a text box to do some calculation for me

=Sum([Used])/Sum([Total])*100

How would I remove the #Num! error

Also how can I round to 2 decimal places, for example 94.7368421052632 to 94.74.


Not finding an answer? Try a Google search.