The 10,000 Mile limit... (1 Viewer)

[Grunners]

Hi there

I've got to calculate the mileage expenses for guys driving X number of miles in a week. The code below I got from an earlier post allows me to set the cost per mile (here £0.40) and just put in the miles covered - it then calculates their expenses in the 'travelcost' box. (I've also got a field that keeps a running total of their mileage.)

Private Sub Mileage_AfterUpdate()
Me.TravelCost = Me.Mileage*0.4
End Sub

Great - however there are two cost bands. Up to 10,000 miles a guy can claim £0.40, over 10,000 miles he can only claim £0.25 per mile. Also when a guy is at say 9900 miles and puts in a claim for 200 miles, it needs to calculate 100 miles @ £0.40 and 100 miles at £0.25. The next week he'd start at 10,100 miles and so be on the lower band for the rest of the tax year.

Any ideas would be much appreciated. I've only been going at this one for a day and a half!!!

Regards

 

[cogent1]

Assumes your Total Mileage field to be Called AllMileage


Private Sub Mileage_AfterUpdate()


Dim RunningMileage As Long
RunningMileage = Me!AllMileage + Me!Mileage

If Me!AllMileage > 9999 Then
Me!TravelCost = Me!Mileage * 0.25
ElseIf RunningMileage < 10000 Then
Me!TravelCost = Me!Mileage * 0.4
ElseIf RunningMileage > 9999 Then
Me!TravelCost = (9999 - Me!AllMileage) * 0.4 + (RunningMileage - 9999) * 0.25
End If
End Sub

 

[Grunners]

MANY THANKS!

I managed to work it out, must have been just before you posted. Useful though - i've learn't quite a bit today.

I'll be using your code though as it's a lot more 'streamlined' than mine...