Help! I live in a world with no pennies!

  • Thread starter Thread starter Henry Smith
  • Start date Start date
H

Henry Smith

Therefore, I have to calculate an item's selling price to the nearest 5
cents. Unfortunately, the sale price I am trying to create is based on a
source that uses pennies. My attempts to calculate a sale price to the
nearest 5 cents has failed. The factors I am working with are Purchase
price, surcharge, monetary conversion factor and profit margin. I have been
able to achieve a sales price to the nearest 10 cents with the following
procedure and the Round function (see code). The input variables are
derived from text boxes on a form. For simplicity in this example code I
have set the variables to a specific value Using Access 2000 Professional.

Any ideas on how to achieve a sales price to the nearest 5 cents will be
greatly appreciated.

Cheers,

Henry

Sub CalculateSalesPrice ()

Dim PurchasePrice As Single

Dim Var1 As Single 'Comment Var1 = surcharge

Dim Var2 As Single 'Comment Var2 = monetary conversion factor

Dim Var3 As Single 'Comment Var3 = profit margin

Dim Cost As Single 'Comment Value before adding profit margin

Dim PreSalesPrice 'Comment Value before rounding

Dim SalesPrice As Single 'Comment this is the sales price

PurchasePrice = 1.27

Var1 = 1.05

Var2 = 1.35

Var3 = 1.27

Cost = PurchasePrice * Var1* Var2

PreSalesPrice = Cost * Var3

SalesPrice = Round(PreSalesPrice,1)

'Comment Sales price for this example equals 1.40

End Sub
 
Henry,

Try this...

Public Function RoundTo5(amnt)
Dim cents As Integer
Dim round As Integer
cents = (amnt - Fix(amnt)) * 100
If (cents Mod 5) > 2 Then
round = 5
Else
round = 0
End If
RoundTo5 = Fix(amnt) + (((cents \ 5) * 5) + round) / 100
End Function

- Steve Schapel, Microsoft Access MVP
 
Thank you very much for the function "RoundTo5".
It works well with my application. I still have to accomplish final
testing, but all seems to work. I understand the "Fix" function and the
"Mod" operator, but I have a question about one character in the final
formula of the function.
For my education how does the forward slash work in the formula:
RoundTo5 = Fix(amnt) + (((cents \ 5) * 5) + round) / 100
work? Specifically how does the formula (cents \ 5) work?

Looking forward to your reply.

Cheers,
Henry
 
Henry,

The \ in an integer divisor. For example...
23\5 = 4
It is, I think, equivalent to Int(x/y)

- Steve Schapel, Microsoft Access MVP
 
Hi Henry,

It is an operator in Visual Basic as Steve said; please refer to the web
site:

\ Operator
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/
vaoprintegerdivide.asp

Please feel free to reply to the threads if you have any concerns or
questions.



Sincerely,

Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "Henry Smith" <[email protected]>
|
| Thank you very much for the function "RoundTo5".
| It works well with my application. I still have to accomplish final
| testing, but all seems to work. I understand the "Fix" function and the
| "Mod" operator, but I have a question about one character in the final
| formula of the function.
| For my education how does the forward slash work in the formula:
| RoundTo5 = Fix(amnt) + (((cents \ 5) * 5) + round) / 100
| work? Specifically how does the formula (cents \ 5) work?
|
| Looking forward to your reply.
|
| Cheers,
| Henry
|
| | > Henry,
| >
| > Try this...
| >
| > Public Function RoundTo5(amnt)
| > Dim cents As Integer
| > Dim round As Integer
| > cents = (amnt - Fix(amnt)) * 100
| > If (cents Mod 5) > 2 Then
| > round = 5
| > Else
| > round = 0
| > End If
| > RoundTo5 = Fix(amnt) + (((cents \ 5) * 5) + round) / 100
| > End Function
| >
| > - Steve Schapel, Microsoft Access MVP
| >
| >
| > On Sat, 18 Oct 2003 18:43:49 +0200, "Henry Smith"
| >
| > >Therefore, I have to calculate an item's selling price to the nearest 5
| > >cents. Unfortunately, the sale price I am trying to create is based
on a
| > >source that uses pennies. My attempts to calculate a sale price to the
| > >nearest 5 cents has failed. The factors I am working with are Purchase
| > >price, surcharge, monetary conversion factor and profit margin. I have
| been
| > >able to achieve a sales price to the nearest 10 cents with the
following
| > >procedure and the Round function (see code). The input variables are
| > >derived from text boxes on a form. For simplicity in this example
code I
| > >have set the variables to a specific value Using Access 2000
| Professional.
| > >
| > >Any ideas on how to achieve a sales price to the nearest 5 cents will
be
| > >greatly appreciated.
| > >
| > >Cheers,
| > >
| > >Henry
| > >
| > >Sub CalculateSalesPrice ()
| > >
| > >Dim PurchasePrice As Single
| > >
| > >Dim Var1 As Single 'Comment Var1 = surcharge
| > >
| > >Dim Var2 As Single 'Comment Var2 = monetary conversion factor
| > >
| > >Dim Var3 As Single 'Comment Var3 = profit margin
| > >
| > >Dim Cost As Single 'Comment Value before adding profit margin
| > >
| > >Dim PreSalesPrice 'Comment Value before rounding
| > >
| > >Dim SalesPrice As Single 'Comment this is the sales price
| > >
| > >PurchasePrice = 1.27
| > >
| > >Var1 = 1.05
| > >
| > >Var2 = 1.35
| > >
| > >Var3 = 1.27
| > >
| > >Cost = PurchasePrice * Var1* Var2
| > >
| > >PreSalesPrice = Cost * Var3
| > >
| > >SalesPrice = Round(PreSalesPrice,1)
| > >
| > >'Comment Sales price for this example equals 1.40
| > >
| > >End Sub
| > >
| >
|
|
|
 
Back
Top