Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Rapid City, South Dakota, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    CurrentReading long integer
    LastMonthsReading long integer
    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.
    Private Sub CurrentReading_Change()
    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

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with a function/procedure (2000)

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

    Private Sub CurrentReading_Change()
    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. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Help with a function/procedure (2000)

    How about this:

    Public Function Price(byValue LastMonthReading, byValue CurrentMonthReading)
    Dim lngUsed as Long
    '
    ' You could put in some code here just in case Current reading < last reading!
    '
    lngUsed = CurrentMonthReading - LastMonthReading
    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
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    May 2001
    Location
    Rapid City, South Dakota, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •