# Thread: Help with a function/procedure (2000)

1. ## Help with a function/procedure (2000)

I am trying to make a billing database and need some assistance.
I want to make a function/procedure that says:

\$15.00 for the first 2000 gallons
\$1.75 per thousand gallons up to 8000 gallons
\$3.00 per thousand gallons after that.

I have the following fields:

AccountNbr long integer
BillingDate date
PaidDate date
Used long integer
Cost currency

I tried the following IF ELSEIF and it works fine, but would have to go a long way to cover all instances.
If Used > 0 And Used < 2001 Then
Cost = 15
ElseIf Used > 2000 And Used < 3001 Then
Cost = 16.75
ElseIf Used > 3000 And Used < 4001 Then
Cost = 18.5
ElseIf Used > 4000 And Used < 5001 Then
Cost = 20.25
ElseIf Used > 5000 And Used < 6001 Then
Cost = 22
End If
End Sub

Lloyd <img src=/S/help.gif border=0 alt=help width=23 height=15>

2. ## Re: Help with a function/procedure (2000)

Select Case has nice options for this: Case Is and Case ... To ...
Try this:

Dim Used1000 As Integer
' Round up to nearest 1000
Used1000 = -Int(-Used / 1000)
Select Case Used1000
Case Is <= 0
cost = 0
Case 1, 2
cost = 15
Case 3 To 8
cost = 15 + (Used1000 - 2) * 1.75
Case Else
cost = 25.5 + (Used1000 - 8) * 3
End Select
End Sub

Or consider using a function:

Function GetCost(Used As Double) As Currency
Dim Used1000 As Integer
' Round up to nearest 1000
Used1000 = -Int(-Used / 1000)
Select Case Used1000
Case Is <= 0
GetCost = 0
Case 1, 2
GetCost = 15
Case 3 To 8
GetCost = 15 + (Used1000 - 2) * 1.75
Case Else
cost = 25.5 + (Used1000 - 8) * 3
End Select
End Function

3. ## Re: Help with a function/procedure (2000)

Dim lngUsed as Long
'
' You could put in some code here just in case Current reading < last reading!
'
Select Case lngUsed
Case <=2000
Price=15
Case <=8000
Price = (lngUsed/1000) * 1.75
Case Else
Price = (8000/1000)*1.75 + ((lngUsed-8000)/1000)*3
End Select

End Function

4. ## Re: Help with a function/procedure (2000)

Problem solved.
Thanks for all your help. I will save replies for future reference.
Thanks
Lloyd
<img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•