Function to convert numeric to a fixed no. of deci

Thread: Function to convert numeric to a fixed no. of deci

1. RB
Member
Join Date
Dec 1969
Posts
30

Function to convert numeric to a fixed no. of deci

Is there a function in Access that can convert a numeric value with decimals, to a fixed no. of decimals?<BR><BR>e.g : 3.125768944 to 3.13 (Rounded up to 2 places of decimal) Or<BR> 10.2571 to 10.257 (Rounded to 3 places)<BR>

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

RE: Function to convert numeric to a fixed no. of

select cast(34.131546876 as decimal(10,2))<BR><BR>the 10 and 2 can be changed. If the number BEFORE the . is less than 10 digits also there will be no problem

3. RB
Member
Join Date
Dec 1969
Posts
30

RE: Function to convert numeric to a fixed no. of

I have the query :<BR><BR>"Select cast(([Fld1]/[fld2]) as decimal(8,2))" where fld1 and fld2 are both numeric<BR><BR>I get the error :<BR><BR>"Syntax error(missing operator) in query expression"<BR><BR>I want the expression "[fld1]/[fld2]" to 2 places of decimal. Can anyone tell me where I&#039m going wrong?<BR><BR>Thanks!

4. Senior Member
Join Date
Dec 1969
Posts
626

RE: Function to convert numeric to a fixed no. of

I don&#039t think CAST is an Access function ... i could be wrong though. In fact I don&#039t think there is a good way to round to two decimals in access. <BR><BR>Maybe something like ...<BR><BR>cDbl(cInt(Value * 100) / 100)<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
626

RE: Function to convert numeric to a fixed no. of

"Select cast(([Fld1]/[fld2]) as decimal(8,2))" <BR><BR>Try<BR><BR>"Select cDbl(cInt([Fld1]/[fld2] * 100)/100)" <BR><BR>

6. RB
Member
Join Date
Dec 1969
Posts
30

RE: Function to convert numeric to a fixed no. of

IT WORKS!! Thanks!

7. Senior Member
Join Date
Dec 1969
Posts
626

RE: be careful

I just test that and ...<BR><BR>cDbl(cInt(100.1156 * 100)/100)<BR>returns: 100.12<BR><BR>but...<BR>Dbl(cInt(100.1146 * 100)/100)<BR>returns: 100.11<BR><BR>which may not be what you want...<BR>

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

Well i guess i missed that bit

i keep forgetting that most people here use Access

9. Richard A. Lowe Guest

Useful fact...

.. if you are going to use 2 decimal places only in SQL Server, use the currency field. Rather than storing float values, it just stores as an integer and / 100 - so you NEVER have to worry about the binary expression of fractions problem (i.e. phantom .00000000005 numbers)<BR><BR>Richard

10. Senior Member
Join Date
Dec 1969
Posts
626

RE: Useful fact...

Actually, Currency Type goes out to 4 decimals. Which still causes problems in the eyes of some bean counters. <BR>"Why is Inventory off by one penny again?" Said the controller. "Because the ledger has a balance of 4,123,567.4451 and the susidiary has a balance of 4,123,567.4449" said the programmer. But thats pretty picky. I guess on SQL server if you want two decimals use the Decimal(10,2) Data type. <BR>

Posting Permissions

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