Currency Rounding for Retail Dollars $$$.x9 (1 Viewer)

Rhino999 · Dec 15, 2013

I need help creating a SIMPLE solution for Retail rounding.
Below are is an Example of Detail lines in a DataSheet Form that represents the price for an Item.
QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$14.0049 = 1 * $10.53 * 1.33

I need the price to end with a Currency Decimal of $$$$$.x9 for all prices.
The example above rounds to $14.00 for with 2 digit rounding or if I use 3 digit rounding $14.01.
I need it to be rounded with a .x9 either $13.99 or $14.09 or to the nearest .x9.
Subsequently, every Detail line we calculate in the Datasheet needs to end with an .x9 for Retail Pricing.
There are thousands of different UnitRates (costs), and the Pct (Percent of Markup) can also change.

Additionally, I sum the Detail Lines and the Totals need to match.
One of the Sum Fields is Calculated as
=Sum(IIf([taxable]=True,Nz([QuantityXUnitRate]),0))
This ends up Totaling to $28.01 which is wrong for 3 Digit rounding of the Detail Line, because $14.01+ $14.01 = $28.02.

I also tried Rounding
=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),0),0))
and after that I still got the wrong amount $28.00.

I tried this with ‘2’ instead of ‘0’ with no difference
=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),2),2)),
I know one of the ‘2’ refers to the Nz and the other to Rounding, but I not sure which is which at this point.

If the last significant digit need to be .09 or .19 or .29 or .39 or .49 or.59 or.69 or.79 or.89 or.99 then everything should Sum up correctly or I hope.

Here is another Example
QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$22.2642 = 1 * $16.74 * 1.33

However, I get the correct Detail Line of $14.01 and $22.26
and using =Sum(IIf([taxable]=False,Nz([QuantityXUnitRate]),0)) same as above, I get the correct amount $36.27.

Can some please help make a with a SIMPLE solution where all Decimals end up with a .x9 and then I’ll tackle the Sum if necessary.

Thanks in advance.

JHB · Dec 15, 2013

I'm a little lost by reading your post, so I need to ask a question.
Should a result as 14.54 be shown as 14.59?
and
a result as 14.00 be shown as 14.09?
or what am I missing?

Rhino999 · Dec 15, 2013

Thank you VERY MUCH for responding!
I did the best job I could do in trying to explain my situation.
I’m glad to answer any questions that you may have to further shed some light on my delema.

I’d like to Round to the nearest .x9 and have .x5 be the tie breaker.
So .x4 or Less Rounds down to .x9 and .x5 or Greater Rounds up .x9.

I’ll use this example:

14.54 should be shown as 14.49.
14.55 should be shown as 14.59.

14.00 should be shown as 13.99.
14.04 should be shown as 13.99.
14.05 should be shown as 14.09.

I hope this helps.

Thanks so much for you help.

spikepl · Dec 15, 2013

So you need to use normal rounding and deduct 0.01 from the result each time. For this to work also with sums, your data type needs to be currency. Also, you need to use normal rounding and not the one used by Access. Google access rounding - there is code on the web to provide normal rounding.

MarkK · Dec 15, 2013

Here's a function that'll round to the nearest whatever, and then you can just subtract your 0.01.

Code:

Function RoundToNearest(Number As Single, RoundTo As Single) As Single
   RoundToNearest = CLng(Number / RoundTo) * RoundTo
End Function

So you can get your result, probably, with math like, . . .

Code:

Function EndsWithANine(Amount as Currency) As Currency
   EndsWithANine = CCur(RoundToNearest(Amount, 0.1) - 0.01)
End Function

Rhino999 · Dec 15, 2013

Thank you very much for responding!

I am calculating QuantityXUnitRate = Quantity * (UnitRate*Pct).
If I'm going to use your Function, I'll need you to be more specific, because I'm not very experienced with Access.
This would be a Public Function, right!

Is this the code that I should use with your Function below?
Me.QuantityXUnitRate = EndsWithANine(Me.Quantity * (Me.UnitRate * Me.Pct)
What do I replace your "Amount" with? It need to be a Field name, right!
What do I do with RoundToNearest? Is that a Field or Value?

Function EndsWithANine(Amount as Currency) As Currency
EndsWithANine = CCur(RoundToNearest(Amount, 0.1) - 0.01)
End Function

What does the 0.1 after the Amount mean?

Thank you for your patients!

Rhino999 · Dec 15, 2013

spikepl, thanks for your response.
Before I posted I looked around for a week and had no success finding anything remotely close to the Rounding I need for this situation. I was not able to find anything at my skill level that I could convert or modify so that I could implement it into my code.

Regards,
Rhino999

MarkK · Dec 16, 2013

Google 'how to call a function in VBA'. There are also article in Access VBA help, like check out the Call, Sub and Function statements. This is a fundamental thing to learn in programming: how and when to use subroutines.
Cheers,

Rhino999 · Dec 16, 2013

I have spent much time (over 40 hours) on this PROBLEM.
I when on this site to get an answer that I can move forward with.
I'm having trouble with you solution.

Additionally, I have yet to find full Syntax on Ccur with all arguments and options.

I haven't found anything using MS Access that referes to RoundToNearest.

Sorry, but I'm dissapointed with your answer.

Geotch · Dec 16, 2013

Check out qry1 - column called Final gives you the answer. Does this work correctly or am I missing something? I was getting some weird rounding for .25, .35, sometimes it would round down, other times round up, that's why I created the "add penny" column for anything that ended in 5.

You'll just need to add these columns to your query where you calculate the price.

MarkK · Dec 16, 2013

Happy holidays. May the spirit of season warm your heart too.

Rhino999 · Dec 16, 2013

Geotch, thanks so much for replying.
Those calculation look correct.
Unfortunately, I'm not using it in a Query.
It is being used in an AfterUpdate statement from Combox Dropdown.
I'm not sure how I can convert it, but I'll study it in greater detail and see if I can incorporate what you did into my Code.

Thanks
Rhino999

spikepl · Dec 16, 2013

@lagbolt

What is it they say ... "no good deed shall go unpunished" :d

Rhino999 · Dec 16, 2013

Hey, if you don't want to really be helpful to those who have less experience that you have, then why are you on here in the first place.

I'm not getting paid for this, I'm trying to helping a friend, for FREE.....

And by the way spikepl, take a look at Geotch response where he demonstrates that you can't do straight round as you suggested.

Have a nice DAY!

Geotch · Dec 17, 2013

Unfortunately, I'm not using it in a Query.
It is being used in an AfterUpdate statement from Combox Dropdown.

I created a form without the drop down box to show you how it could work. Look at qry2 where I replace the field CalcCost with this formula:

CalcCost: [quantity]*([rate]*[percent])

The rest of the query works the same. Hope that helps.

Rhino999 · Dec 17, 2013

Geotch, thank you very much for your time, effort and your clever solution.

I see that it calculates the Currency correctly and has the desired result.

It's much appreciated!

Regards,
Rhino999

Rhino999 · Dec 20, 2013

Geotch, again, I am very appreciative for your efforts to solve my problem.

For the many who looked at this post and had a similar situation that they wanted to resolve, here is another solution that someone else gave me.

QuantityXUnitRate = CCur(Format(Quantity * (UnitRate * Pct) / 10, "0.00") * 10 - 0.01)

Thanks to all!

Currency Rounding for Retail Dollars $$$.x9 (1 Viewer)

Rhino999

Registered User.

JHB

Have been here a while

Rhino999

Registered User.

spikepl

Eledittingent Beliped

MarkK

bit cruncher

Rhino999

Registered User.

Rhino999

Registered User.

MarkK

bit cruncher

Rhino999

Registered User.

Geotch

Registered User.

Attachments

MarkK

bit cruncher

Rhino999

Registered User.

spikepl

Eledittingent Beliped

Rhino999

Registered User.

Geotch

Registered User.

Attachments

Rhino999

Registered User.

Rhino999

Registered User.

Similar threads

Users who are viewing this thread