Number Formats

[jennilein]

I have several monetary fields in an access table. They are formated as a double - currency - 2 decimal places.

My problem is when I perform calculations with these fields it is really calculating with however many decimal places the number might have - although in my table I only see 2 spaces. This confuses many people because if I am multipling two columns to create another column - people who want to double check this value are only using the two decimal points they see and it does not match the value in the new column.

How can I set the number format to only display 2 decimal spaces and ONLY use the 2 decimal spaces to calculate? In other words it should round up to the 100th decimal space.

Thanks,
Jennifer

 

[benbakelaar]

i'm not sure if this would apply to your problem, but could you set the monetary fields as Currency data type? this might allow you to limit storage to a certain number of decimals.

also this note from the DecimalPlaces property help file entry...

Note The DecimalPlaces property setting affects only the number of decimal places that display, not how many decimal places are stored. For example, if you set the DecimalPlaces property to 2 for a Number field with the FieldSize property set to Integer, it will display with two decimal places, but because only whole number (integer) values will be stored, zeros will fill the two decimal places. To change the number of decimal places that will be stored, you must change a Number field's FieldSize property.

 

[jennilein]

I tried to change the data type to currency and, unfortunately, it still is storing more than two decimal spaces. In the table, I only see 2 spaces, however it is still storing more than 2 and using more than 2 to calculate.

Do you have any other suggestions? This is really annoying because I am working with revenue numbers and it's ultimately over-stating my revenue - because no one else tracks more than two decimal spaces?

Help!

Thanks,
Jenni

 

[benbakelaar]

ROUND
Rounds a number to a specified number of digits.

Syntax

ROUND(number,num_digits)

Number is the number you want to round.

Num_digits is the number of digits you want to round to. Negative rounds to the left of the decimal point; 0 (zero) rounds to the nearest integer.

 

[jennilein]

That sounds like it would work, but where do I put this command?

 

[benbakelaar]

can you post the code that you are using to perform the calculations? all you really need to do is add the ROUND() function to the monetary field variables... so Round(moneyfield1,-2) * Round(moneyfield2,-2).

although, i don't know why the help file entry is saying negative rounds to the left... try positive if negative doesnt work i guess?

 

[jennilein]

It's really the fields in the table that are already incorrect. The file I use to import apparently hold more than just 2 decimal places.

However, here is one example of a calculation:

UPDATE [Current Source Data] SET [Current Source Data].nr_03 = [net price]*([qty-grs]-[qty-ret])
WITH OWNERACCESS OPTION;

I typically do not work in the SQL view - I just use the design view of the query.

Thanks,
jennifer

 

[benbakelaar]

after doing a little research, and again i'll say im just a beginner, but it doesn't seem like access can modify the actual value of the field, just manipulate the calculated value...

i started a blank database, though, and noticed there is a "Decimal" type number variable... in it, you can set the "Scale" to 2, which refers to the number of places the field will store to the right of the decimal. perhaps you can make a duplicate copy of the table where your values are stored, and then experiment with changing the number type of the field to Decimal with a Scale of 2.

 

[Pingwin101]

Thank you to everyone in this thread for the info you provided! I came searching and found what I needed. Thanks especially to the person who recommended the "Field size" solution--i changed my field size to integer and it converted my data to whole numbers...which is what i came looking for in the first place. None of this may have helped the person who originally asked the question, but it helped me! Thanks again!!
Another satisfied customer.

 

[Terpsfan]

In the control source of the calculated field I used this function =CCur(Format([Principal2]+[Principal3],"Fixed&quot), which will always show as 2 decimal places no matter how many decimals exist in the values being calculated. Principal2 and Principal3 are double currency fields. You also have to specify currency format in the calculated field property with 2 decimal places.

CCur(Format([Principal2]+[Principal3],"0.00&quot) also worked in code.

 

[janelle17]

=CCur(Format([Principal2]+[Principal3],"Fixed")) This was helpful because it eliminated the trailing decimals but is there a way to pull the two decimals without rounding?

Thanks,
Janelle17

 

[Terpsfan]

There may be a more elegant solution out there but this will work. You would substitute principal and balance with your own values. This should round down to 2 decimals.

[COLOR=blue]
=CCur(Left([Balance]*[Principal],(Len([Principal]*[Balance])-(Len([Principal]*[Balance])-InStr(([Balance]*[Principal]),".")))+2))
[/color]

 

[Terpsfan]

Actually this is a better way to do it in a form. You would do it in the form current event.

[COLOR=blue]
Dim z As Double
Dim x As Double
Private Sub Form_Current()
    x = [Balance] * [Principal]
    z = CCur(Left(x, (Len(x) - (Len(x) - InStr((x), "."))) + 2))
    Text17 = z
End Sub
[/color]