Help Rounding up Values

[gselliott]

I am wanting to set up a form that calculates the insurance cost for delivery of goods depending on their value. The user will enter the value of the goods and i am wanting to be able to automatically calculate the value. Upto the first £1000 the cost is a set charge and for each additional £1000 an additional cost of £5.00 is added.

The problem i am having is that i am not getting an accurate total if the user does not enter exactly £1000, £2000, £3000 etc. (eg if they entered £1500). I think i need to some how need to round up the total for ExtraInsurance1 = (Value / MaxValue).

This is what i have so far:

Dim Value2 As Currency
Dim ExtraInsurance1 As Integer

If Me!Value > Me!MaxValue Then

ExtraInsurance1 = (Value / MaxValue) * ExtraInsurance
Value2 = ExtraInsurance1 - ExtraInsurance
InsCharge = Value2 + InsurancePrice

Else

InsCharge = InsurancePrice

End If

TotalCharge = ChargeToCustomer + InsCharge

End Sub

Any help would be very much appreciated as i am banging my head against the wall now!!

Cheers!

 

[Rich]

Totally off the top of my head, I don't have time to test it
Select Case MyField
Case 1,1000
TotCost = MyField+5
Case 1001,2000
TotCost = MyField+10
etc
End Select

 

[FoFa]

using your example, which maybe to simple, but I will use it anyway. Why not setup a table with the minimum values as a key and use SQL to return the cost to you.

1000 10
2000 15
3000 20

One time setup and easy to change if rates differ in the furture and then you can use >= and < on the value

Just a thought.

 

[Rob.Mills]

Dim Value2 As Currency

If Value2<=1000 then
InsCharge= set amount
Else
InsCharge=set amount + 5*(int(Value2/1000))
End if


This should incrementally increase the insurance price based on the value. This starts the rates at exactly the 1000 mark meaning 2,000 would add 10 to the amount.

Should it start at say 2,000.01?

 

[OPMCoordinator]

i had a similar situation once and found that the woorksheet function FLOOR would have been perfect. But of course does not work in vba!
i ended up using FIX. (could use INT as well)

Sub TestThisOut()
Dim Value2 As Integer
Dim ChargeAmount As Integer
'Value2 = InputBox("Enter Amount")
If Value2 <= 1000 Then
ChargeAmount = 100 'or whatever your min charge is
Else
ChargeAmount = 100 + ((Fix((Value2 - 1000) / 1000) + 1) * 5)
End If
'MsgBox (ChargeAmount)
End Sub

This assumes that 2000 is into your third bracket of charging ie 100 + 5 + 5. 1999 would be 100 + 5.

hth
Mike C

 

[gselliott]

Thanks for all your help i'll give it a try.