Round Down in Access

[vonoventwin]

Everytime I enter currency in a currency textbox. It automatically rounds up. For instance, $172.845 will round to $172.85. How do I make it round down instead? Anyone know?

[Static]

u need to calc it on your own

offhand the only way I can remember how to do this is actually turn the number into a string then back

(FYI: The actual value is still 172.845 but access rounds up for display purposes only. when the number is used for calcs or called back up.. it is in its original form with as many decimals as it had originally)

Dim NUM As Currency
Dim TMP As Double
 
TMP = 172.845
NUM = CCur(Left(CStr(tmp),InStr(CStr(tmp),".")+2))

[vonoventwin]

Can you send me an example. It's driving me nuts!

[RobDog888]

If this is in an Access form textbox then just set the Decimal Places property to 3 or more depending on the accuracy you desire.

[dannymking]

The table below the form will also need to be changed as the issue could be at this point.. (InputMask, Format for example)

[vonoventwin]

It is an Access form textbox. I want two decimals. But it's rounding 43.455 to 43.46 and even if the number is 43.455 I want it to truncate and show 43.45

[vonoventwin]

The table below the form will also need to be changed as the issue could be at this point.. (InputMask, Format for example)

Currency format with no input mask

[dannymking]

Check your underlying table to see what the overall properties are for the field to which this text box is bound.

If no joy there then you will have to use vba code to capture the value as it is entered into the textbox (either through record navigation or user interactio) and display it as a string instead. This way you can keep the two decimal places and lose the additional.. But it is bad design and will ultimately lead to further problems.

[RobDog888]

There is also a Format property for the textbox which will allow you to create a custom display format truncating the third or more decimal place without rounding.

[dannymking]

Cross post..

change the currency to number with field size of single instead.

[RobDog888]

Good idea Danny. That will retain the precision of the original number. Then you can format it however you want without it rounding at all.

[vonoventwin]

Good idea Danny. That will retain the precision of the original number. Then you can format it however you want without it rounding at all.

So now the original number stays the same, great. But how do I format it to just show 45.26 instead of showing 45.26745674??

[vonoventwin]

How do I use this: I tried but can't get it working. Sounds like what I want to do. I want to ignore any numbers entered after the 2 decimal places.

To round or truncate numbers to two decimal places, create a new module and add the following functions.

'******************************************************
   ' Declarations section of the module
   '******************************************************
 
   Option Explicit
   Const Factor = 100
 
   '=====================================================
   ' TruncAU is designed to be added to the
   ' AfterUpdate property on a form control.
   '=====================================================
   Function TruncAU(X As Control)
      X = Int(X * Factor) / Factor
   End Function
 
   '=====================================================
   ' RoundCC and TruncCC are designed to be used in
   ' expressions and calculated controls on forms and reports.
   '=====================================================
   Function RoundCC(X)
 
      RoundCC = Int (X * Factor + 0.5) / Factor
 
   End Function
 
   Function TruncCC(X)
      TruncCC = Int (X * Factor) / Factor
   End Function

Examples of Using the Round and Truncate Functions

Example 1
Use the TruncAU() function to the AfterUpdate property of a form:
1. Open the database
2. Create a new module called Rounding, and type the procedures in the preceding section.
3. Open your form in Design view, and add the TruncAU() function to the AfterUpdate property of the field (NUM)
Form: Form1
--------------

Control Name: NUM
      AfterUpdate: =TruncAU([NUM])

If a user accidentally enters $23.055 instead of $23.05, the TruncAu() function catches the mistake and changes the value to $23.05.

[vonoventwin]

This is exactly what I want to do Truncate. I don't want the extra numbers typed in. just the first 2 after the decimal (ex: 45.6577758, I want 45.65)

TRUNCATE

(v.) To cut off the end of something. Usually, the term is used to describe a type of rounding of floating-point numbers. For example, if there are too few spaces for a long floating-point number, a program may truncate the number by lopping off the decimal digits that do not fit: 3.14126 might be truncated to 3.14. Note that truncation always rounds the number down. If the number 1.19999 is truncated to one decimal digit, it becomes 1.1, not 1.2.

[Ecniv]

strVar="45.553225" 'or variable of number
debug.print left(strVar,instr(1,strVar,".")+2)

[loonighan]

This works for me:

Function RoundDown(dVal As Double, Decimals As Integer) As Double
Dim Factor As Double, fVal As Double
Dim iVal As Integer
    Factor = Exp(Log(10) * Decimals)
    fVal = dVal * Factor
    iVal = Int(fVal)
    RoundDown = iVal / Factor
End Function

Use it in an AfterUpdate event.