round up calculated field to number divisible by $0.05

  • Thread starter Thread starter Jeannie Slater via AccessMonster.com
  • Start date Start date
J

Jeannie Slater via AccessMonster.com

HI
In a query, I have fields: [quantity], [price] AND then a calculated field for quantity x price called [total].

When I do the following calculation examples, I see (2 examples):

quantity= 1.7, price= $.20, total calculates as $.34
quantity=1, price= $.75, total calculates as $.75

How can I round up $.34 to the next highest number that is divisible by .05? I would NOT want the $.75 to round as it is already divisible by .05.

I would want to see:

quantity=1.7, price= $.20, total calculates (.34) but rounds to $.35
quantity-1, price= $.75, total calculates (.75) and stays at $.75

I don't know programming, I just use queries to do my calculations. I am a simple girl

Thanks, Jeannie
 
Try pasting the following function into a module and then calling it as needed.

Public Function fRoundUp (dblNumber As Double, _
Optional intPlaces As Integer) As Double
fRoundUP = -Int(-dblNumber * 10 ^ intPlaces) / 10 ^ intPlaces
End Function

Calling it this way

fRoundup(CCur([Quantity]*[Price])*20)/20

If you wanted to round up the next dime, it would be simpler

fRoundUp([Quantity]*[Price],1)
 
HI
Thanks for the information....I set up the module, I entered the "calling" into the control on the form. I am getting strange results:

Examples:

Quantity of 4 times $.60 each is giving $2.88.
Quantity of 4.3 times $.20 is giving $.88

The database I am working on is for our lunchroom at work. Items could be priced like: .75 for pop, .20 per 1 oz of salad. The bosses don't want to deal with pennies, the smallest denomination is to be a nickel. That is why I need a way to round up to the nearest .05.

Any other advise?

Thanks for all your help. Jeannie.
 
HI again,

Just heard from another forum and was given this solution. It works just the way I had hoped.

Expr1: IIf((([quantity]*[price])*100) Mod 5=0,[quantity]*[price],([quantity]*[price])+(0.05-(((([quantity]*[price])*100) Mod 5)/100)))

Thought I would pass this information on.

Thanks so much for any time and your help.

Jeannie
 
Don't know what you are entering or where, but I can say that the formula works
for me.
 
Back
Top