Prevent storing more than 2 decimal places

[Debbie]

Hello everyone,
I have spent HOURS on this so maybe someone could help. I have a table with
the field AmountEarned and it is defined as:
Datatype = Currency
Decimal = 2
Format = Currency

I have a program that calculates the AmountEarned. The equation is:

AmountEarned = (TotalSales - TotalCost) * CommRate

Total Sales and TotalCost are defined exactly as AmountEarned
CommRate is a decimal (Example - .01)

In my program, the calculation yields the number 20.196 and that is what's
being stored in the table. I want 20.19 to be stored in the table. I used
the Format property to display 2 digits past the decimal but in doing that,
it rounds it to 20.20 and then if you click in the AmountEarned table, it
actually shows 20.196. The problem with using the Format property is it
rounds up and I don't want that. I have tried everything I can think of.
Does anyone have any ideas as to what I'm doing wrong? Thanks so much.
Debbie

 

[John W. Vinson]

In my program, the calculation yields the number 20.196 and that is what's
being stored in the table. I want 20.19 to be stored in the table. I used
the Format property to display 2 digits past the decimal but in doing that,
it rounds it to 20.20 and then if you click in the AmountEarned table, it
actually shows 20.196. The problem with using the Format property is it
rounds up and I don't want that. I have tried everything I can think of.
Does anyone have any ideas as to what I'm doing wrong? Thanks so much.
Debbie

Round it down before storing:

AmountEarned =CCur(Fix((TotalSales - TotalCost) * CommRate * 100)/100.)


John W. Vinson [MVP]

 

[Debbie]

John,
Thanks so much, it worked perfectly! The only thing is I'm not sure I
understand how it works! Does the Fix get rid of the decimal places and then
dividing by the 100 puts them back in? There wasn't much in the Help on Fix.
Thank you so much.
Debbie

 

[deborah's mail]

in article (e-mail address removed), Debbie at

 

[John W. Vinson]

John,
Thanks so much, it worked perfectly! The only thing is I'm not sure I
understand how it works! Does the Fix get rid of the decimal places and then
dividing by the 100 puts them back in? There wasn't much in the Help on Fix.
Thank you so much.
Debbie

You need to have the VBA window open to get the help:

Int, Fix Functions


Returns the integer portion of a number.

Syntax

Int(number)

Fix(number)

The required number argument is a Double or any valid numeric expression. If
number contains Null, Null is returned.

Remarks

Both Int and Fix remove the fractional part of number and return the resulting
integer value.

The difference between Int and Fix is that if number is negative, Int returns
the first negative integer less than or equal to number, whereas Fix returns
the first negative integer greater than or equal to number. For example, Int
converts -8.4 to -9, and Fix converts -8.4 to -8.


John W. Vinson [MVP]