Stop rounding, Please

[dunnjt]

Am using a subform based on a query based on a table. I have set the appropriate field properties in the table to Currency & 2 decimal places.
Here is the problem. When there is a decimal value (usually 2 places) in the Quantity field( E.g. 22.39) Multiplied by another decimal value (also usually 2 places)in the Rate field, the resulting calculated control on the form rounds the number up. If there are not 2 decimal places in both these fields, the resulting calculation is NOT rounded. I need this calculation to NOT be rounded regardless of the number of the decimal places in the Quantity and Rate fields. Never seen this before. Can anyone make a suggestion? I've exhausted my bag of tricks and need expert help.
Thanks,
Jerry D.

 

[BSman]

Have you tried multiplying the two values into a variable and then chopping off the digits past the first two decimal places before you move the result into your currency field? Or you could make an equation that would multiply the two fields, then multiple the result by 100, then turn it into an integer, then divide by 100 and put the result into your currency field.

 

[missinglinq]

What format is the calculated control field set to?

The Missinglinq

"It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[dunnjt]

The Calculated control called "Extension" is set to "Currency". I may be wrong but the problem may be in the query. If I enter "dummy data" to see if the query does or does not round the resulting calculation, if there are 2 decimal places in the Quantity and Rate fields, the query will round in the resulting "Extension field". If there are not 2 decimal places in each of these fields, the query will not round.
I am at a loss. This seems like it should be simple to solve. Any other suggestions would be appreciated.
Thanks,
Jerry D.

 

[dunnjt]

When I multiply the Quantity times the Rate I get the calculated control called "Extension". How might I "chop off" any digits after in excess of two decial places? There are only about 3 or four properties displayed for this extension control. Where would I chop pff the digits and wouldn't Access round off the numbers anyway, despite the dropping off of the digits.
Looks like it will be an interesting MOnday.
Thank you,
Jerry

 

[mph1]

Hi Jerry,

First, your Quantity field should not be dsignated as currency. What is confusing to me is how can quantity be something other than a whole number (no decimal)?? If you have Quantity x Price (where price would be currency), not sure how the total can be more than 2 decimal places.


However, maybe "Quantity field( E.g. 22.39)"as you indacte is an accurate description.

Again, I would appreciate knowing how the Quantity can be something other than a whole number.

Good Luck.


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[scriverb]

Give this VBA code a try. I setup a form with Quantity, Rate, and Extensions controls. Entered your example and this works fine.

Me![Extensions] = CDbl(Mid$(CStr((Me.Quantity * Me.Rate) * 100), 1, InStr(1, CStr((Me.Quantity * Me.Rate) * 100), ".") - 1) / 100)

Basically this is converting the initial Extensions value to a string, multiplying by 100 to move the decimal, stripping off the characters to the left of the decimal, dividing by 100 to move the decimal back to the correct location, and then converting the string back to a Double precision currency.

Let me know if this works for you.


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dunnjt]

About the Quantity field having decimal places- an example of this would be billable hours, The quantity could be 5.75 hours. Another example might be when we buy gasoline, we usually don't buy by the whole gallon but by parts of a gallon e.g. 22.25 gallons.
Thanks for the suggestions,
Jerry

 

[mph1]

I appreciate your claification of Quantity. I was thinking in terms of inventory where you only have whole pieces. However, based on your 2 examples of time and gallonls, you would still want to have the total cost to be in dollars and cents which would still have be to rounded off two decimal places. The general convention of rounding is
0 - 4 ---- round down
5 - 9 ---- round up.

Also, you might want to change the property of the price field to numeric. Then the field that shows the total, unbound, could be currency with two decilmal places.


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[TheAceMan1]

How are ya dunnjt . . . .

If your number will not be negative, how about the good ol:

[blue]Int(100 * num)/100[/blue]

See Ya! . . . . . .

 

[dunnjt]

For the last several weeks, this problem(rounding and too many decimal places) has taken a back seat to other items more pressing. However. I am still having the same problem with a rounding and decimal problem and am desperately looking for a solution.
Can anyone make a suggestion? I have attempted multiplying and dividing by 100 (thanks for the suggestion Bob S.) but that returns the same number unacceptable decimal places and rounding as the original extension number. Maybe this is really the answer but I am missing something in the process.
OPen to any suggestions.
Thank you - I hope there is a solution out there.
Jerry

 

[roppetech]

Jerry,

Off the top of my head, I'd try this:
cdbl(mid(1,len(cstr([quantity]*[price]))-1,cstr([quantity]*[price])))


- RoppeTech

 

[dunnjt]

Roppetech- thank you again for this response. Could you "translate" your solution into other words? I don't write code but may possibily be able to get Access to do what your code is doing if I could understand what it means. BTW-[Quantity] field will never be more than 1 decimal place but I don't think that in the final result it makes any difference if there are two or just one decimal place. Does the fact that these are calculated controls afffect the final outcome? I've tried everything I know about properties in the query and table to no avail. What am I missing?
Your help is appreciated - I hope you are a patient person!
Thank you, Jerry

 

[scriverb]

The formula by Dunnjt has the wrong syntax for the Mid function. The expression that I provided previously works in all of the instances that you describe in my testing here. Have you tried to use it in situation. Please provide any values that you think it does not calculate correctly. Be glad to take a look and see what is wrong. But, from my testing the express works as needed.

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[TerryEA]

Bob Scriver's post is the one for me. If anyone in here has ever "written" reasonably powerful applications in old DOS databases, where sometimes there was no facility to write code, but only to paint screens and specify fields, they will have had to learn that sort of logical approach anyway, ie:

1) multiply by 100 - to lose the decimal places
2) convert to text - so that you can remove leftmost characters
3) reconvert to a numeric field

and Bob's your uncle (as they say)

Used to be standard stuff, if you needed to maintain values at a set number of decimal places.

Tel

Tel

 

[scriverb]

Thanks Terry for the vote of confidence. I misquoted the handle in my last posting. It was RoppeTech that had a slight syntax error in the expression. I've done it before when using both Mid$ and Instr as they reverse themselves. No biggie but just wanted to point it out.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[ineuw]

Currency type is what is known as a scalar type double and calculates values to 4+ decimal points, whether you like it or not. You can use the Round() function which is much more elegant than any of the listed suggestions (I tried them all), but my suggestion would be is just forget it. Either you will be out by an ever increasing amount, or eventually it will round itself as it should.

Instead, rent the Superman film in which Richard Pryor is scheming to get in on the action and get rich when he realizes that banks use scalar calculations and pocket the difference between 2 and 4 digit decimal calculations as they cheat the public.

Signed: an accounting software designer and programmer. :-D