round up calculated field to number divisible by $0.05

[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

 

[John Spencer (MVP)]

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)

 

[Jeannie Slater via AccessMonster.com]

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.

 

[Jeannie Slater via AccessMonster.com]

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

 

[John Spencer (MVP)]

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