Two Questions

[elliot315]

1. How do I limit the currency decimal places to 2, it allows me to enter more than 2 decimals places and then it rounds to the second place.. I want to not be allowed to enter more than two. Access is showing a number with two decimal places, but its storing the number with more decimals places and for the process I need Access to use the number with 2 decimal places (the one it shows, not the one its store). And if a person enters a number with more than 2 decimal places to show a MsgBox Warning

2. Is there a formula to get to the specified decimal places without rounding that last decimal place... for example
I have a formula that multiplies an amount of money by 2%... and the result comes with more than 2 decimal places, like 2345.87 * .02 = 46.9174
I want to retain 46.91 but Access is storing 46.9174 and when completing my formula its like it is using 46.92 :S
or if I have to add two numbers... like 43.942 and 14.944 it came up with 58.89 but I need 58.88 my reports have to match with a report from the bank and because of this, it's not matching!!!

 

[gemma-the-husky]

1. if a user enters more than two dps

you perhaps need to validate this at the time the input is entered. if there are only 2dps, then multiply the no by 100, convert it to an integer, and divide again, and you should get the same

so

if clng(mynumber*100)/100<>mynumber then
'errror - 2 many dps
end if

2. use round function in the calculation before storing the number
so

commission = round(invamount*2%,2)

 

[elliot315]

that's the problem i don't want to round... I always want the lower amount.

 

[gemma-the-husky]

the clng function will get around this as it truncates

so with 12.416

finalvalue = clng(mynum*100)/100

will do

12.416*100 = 1241.6
clng wil truncate to 1241
divide/100 will give 12.41

 

[elliot315]

it will truncate it and store the truncated number?

 

[gemma-the-husky]

yes if you do the sum in the after update event

OR

do it in the before update, and tell the user he has entered too many decimal places

 

[elliot315]

could you give the code for that?
the user has to enter the the following amounts
Balance, Interests, Recharges, Penalties, and TOTAL is the sum of those fields
how do I write the code in each field text to recognize the user had enter a wrong amount?

and thank you for keeping track of the thread and reply all my messages

 

[gemma-the-husky]

in the before update for any field put

fieldname_beforeupdate(cancel as etc) - this is the sub header generated by access

dim chkvalue as currency

chkvalue = clng(fieldname*100)/100 'do the test calculation
if chkvalue<>fieldname then
    msgbox("Too many decimal points")
    cancel = vbcancel
    'important this tells access to cancel and not accept the entry
    exit sub 'exit because the entry was wrong
end if

'if you get here the number is valid, and you can do anything else you need with the number
other code
----
----
----

 

[elliot315]

Thanks GEMMA..!