How many halves?

[Malc]

I am writing an invoicing system which will bill customers by weight, and in
particular by the half kilo. (or half pound in the USA)

A typical tariff may be - First 5 kilos charged at $10 and every additional
1/2 kilo charged at $1.20

Does anyone have a function that can calculate the number of half kilo over
a certain weight.

Using the example above, 7 kilos would be charged at $10 plus 4 * $1.20

The data entry screen allows for one decimal place which means I get entries
like 10.7 kilos. This would equate to - First seven kilos @ $10 and 8 half
kilos. (10.7 rounded up to 11).

This is my current function which doesn't work!

Function nohk(wt)
Dim ret, nohk1, nohk2, tenth
'calculate the number of half kilos (nohk) in the value passed

ret = InStr(wt, ".")
nohk1 = 0
nohk2 = 0

If ret <> 0 Then
nohk1 = Left(wt, ret) / 0.5
tenth = Mid(wt, ret + 1, 1)

Select Case True
Case tenth > 1 And tenth <= 5
nohk2 = 1
Case tenth > 5 And tenth <= 9
nohk2 = 2
End Select

Else
nohk1 = wt / 0.5
End If

nohk = (nohk1 + nohk2)
End Function

 

[Graham R Seach]

Malc,

curPrice = CCur(IIf(m>q, p1 + ((IIf(m - q < 0, 0, Round(m - q)) * 2) * p2),
(p1 / q) * m)))

Where m = the number of kilos purchased.
q = the quota (the first 7, for example).
p1 = the price for the quota.
p2 = the price for each 1/2 kilo thereafter.

I assume that if m < q, then the price is calculated pro-rata. If not, we
just need to change the bit after the last comma in the formula.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Malc]

Graham,

Thanks very much

Malcolm.

Malc,

curPrice = CCur(IIf(m>q, p1 + ((IIf(m - q < 0, 0, Round(m - q)) * 2) * p2),
(p1 / q) * m)))

Where m = the number of kilos purchased.
q = the quota (the first 7, for example).
p1 = the price for the quota.
p2 = the price for each 1/2 kilo thereafter.

I assume that if m < q, then the price is calculated pro-rata. If not, we
just need to change the bit after the last comma in the formula.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


and

 

[Malc]

Thanks again but I have run into a problem.

Consider 5.4 kilos with a minimum charge for 5 kilos. This leaves .4 of a
kilo which would be charged as the first half kilo above 5 kilos. The
function round(.4) results in 0

I have used more meaningful variables to illustrate the problem.

wt = Weight of consignment

Minwt = minimum wt

Min= minimum charge

adh = additional half kilo charge



curprice = CCur(IIf(wt > minwt, min + ((IIf(wt - minwt < 0, 0, Round(wt -
minwt)) * 2) * adh), min))

Malc.

 

[Graham R Seach]

Malc,

OK, I didn't realise you wanted to always round UP. I assumed you wanted to
round up when 0.5 or greater, and down if <0.5.

I haven't exhaustively tested it, but try this:
curPrice = CCur(IIf(m > q, p1 + p2 * 2 * IIf(Int(m - q) < m - q, Int(m - q)
+ 1, Int(m - q)), m * (p1 / q)))

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html