Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Washington, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Correct syntax for Select Case statement (2003)

    What is the corrrect syntax to do a between within a funciton (Select statement)

    Say I want to see all records that $ amt is between >100,000 and <= 500,000 when I use the funciton below my results differ from when I use the above mentioned between statement in a query. What is the correct syntax?

    karen

    Here is my current function:
    Public Function InHouseExpenditures(Labels As Currency)
    'Purpose : Converts InHouseExpenditures value into Label Groupings
    'Returns :
    'Developer : Karen F. Schaefer
    'Created : 7/13/2005 11:21
    On Error GoTo Err_InHouseExpenditures
    Dim pLabels As String
    Dim strtemp As Currency

    strtemp = Labels
    Select Case strtemp
    Case 0 To 100000
    pLabels = "0 - 100"
    Case 100001 To 500000
    pLabels = "101 - 500"
    Case 500001 To 1000000
    pLabels = "501 - 1,000"
    Case 1000001 To 5000000
    pLabels = "1,001 - 5,000"
    Case 5000001 To 10000000
    pLabels = "5,001 - 10,000"
    Case 10000001 To 25000000
    pLabels = "10,001 - 25,000"
    Case 25000001 To 50000000
    pLabels = "25,001 - 50,000"
    Case 50000001 To 100000000
    pLabels = "50,001 - 100,000"
    Case Is > 100000000
    pLabels = "> 100,000"
    End Select

    InHouseExpenditures = pLabels
    Exit_InHouseExpenditures:
    On Error Resume Next
    Exit Function

    Err_InHouseExpenditures:
    Select Case err.Number
    Case 0
    Resume Exit_InHouseExpenditures:
    Case Else
    MsgBox err.Number & " " & err.Description, vbExclamation, "Error in module Module1 - function InHouseExpenditures"
    Resume Exit_InHouseExpenditures:
    End Select
    End Function


    --------------------
    Thanks,

    K

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

    Re: Correct syntax for Select Case statement (2003)

    You don't say how the result is different. Since Select Case stops at the first case that "fits", you could use this:

    ...
    Select Case strtemp
    Case Is <= 100000
    pLabels = "0 - 100"
    Case Is <= 500000
    pLabels = "101 - 500"
    Case Is <= 1000000
    pLabels = "501 - 1,000"
    Case Is <= 5000000
    pLabels = "1,001 - 5,000"
    Case Is <= 10000000
    pLabels = "5,001 - 10,000"
    Case Is <= 25000000
    pLabels = "10,001 - 25,000"
    Case Is <= 50000000
    pLabels = "25,001 - 50,000"
    Case Is <= 100000000
    pLabels = "50,001 - 100,000"
    Case Else
    pLabels = "> 100,000"
    End Select
    ...

Posting Permissions

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