round up to nearest .05 on calculated field in query 1

[SkunkyToo]

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

 

[GingerR]

you can just put this in your query as a calculated field. sub in your field names for txt1 and txt2:

=IIf((([txt1]*[txt2])*100) Mod 5=0,[txt1]*[txt2],([txt1]*[txt2])+(0.05-(((([txt1]*[txt2])*100) Mod 5)/100)))

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkunkyToo]

GingerR,

Thanks so much! That did the trick. I was so stuck on this single issue and you have made it possible for me to continue on.

Thanks again, Jeannie.