Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: Select Case Function help

    I have a Function(module):

    Code:
    Function Area(MATERIAL As String) As String
    Select Case MATERIAL
         Case "SR-A-*"
              Area = "TEST"
         Case "SR-*-A-*"
              Area = "TEST2"
         Case Else
              Area = MATERIAL
    End Select
    End Function
    What Im trying to do here is.. if there is data like so:

    SR-A-02
    SR-03-A-23
    SR-A-45
    SR-A-048

    then display:

    TEST
    TEST2
    TEST
    TEST

    I tried this:

    Case "SR-A-*"
    Area = "TEST"
    Case "SR-*-A-*"
    Area = "TEST2"

    with the "*" astricks.. but that doesn't seem to work

    any ideas?

    thanks
    Last edited by TonyT; 08-25-04 at 16:17.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    with the "*" astricks.. but that doesn't seem to work

    any ideas?
    Wild cards only work with the Like Statement, and The Select statement only accepts the operators (=,<>,>,<,=>,>=,=<,<=) You can do this you just have to trick it. You can make this work with the LIKE operator in the select case construct by testing for the True condition.

    Code:
    Sub TestCase()
    
    strMATERIAL = "SR-3-A-23"
    
    Select Case True
         Case strMATERIAL Like "SR-A-*"
              Debug.Print "TEST"
         Case strMATERIAL Like "SR-*-A-*"
              Debug.Print "TEST2"
         Case Else
              Debug.Print strMATERIAL
    End Select
    
    End Sub
    ~

    Bill

  3. #3
    Join Date
    Aug 2004
    Posts
    1

    re: Select Case function Help

    Ok... I've done some testing, and the following code depends on the fact that the 4th letter is either an 'A' or the number '0'.

    Code:
    Function Area(MATERIAL As String) As String
    Select Case Mid(MATERIAL, 4, 1)
         Case "A"
              Area = "TEST"
         Case "0"
              Area = "TEST2"
         Case Else
              Area = MATERIAL
    End Select
    End Function
    You can put as many "case" criterea on the same line, as in:

    Code:
         Case "A","B","C"
    Hope this helps!

    gcomyn

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    Not quite sure I understand the concept.. but

    I tried as you suggested:

    my mod (modArea) -

    Code:
    Function Area(MATERIAL As String) As String
    
    MATERIAL = "SR-A-23"
    
    Select Case True
        Case MATERIAL Like "SR-A-*"
            Debug.Print "TEST"
        Case MATERIAL Like "SR-*-A-*"
            Debug.Print "TEST2"
        Case Else
            Debug.Print MATERIAL
    End Select
    End Function
    Now in my query:

    Field:
    Area1: Area([tblInfo].[Material])

    Im getting nothing but blanks in my field. and not the desired "TEST" and "TEST2"

    -----

    I have more data then the mention aboved. so will this work for different samples?

    ie..

    SR-EC*
    SR-G*EC*
    SR-T-*
    etc...

    Thanks for your help

  5. #5
    Join Date
    Jul 2003
    Posts
    292
    I believe I will go a different route here..

    Text1 = IIf(txtname Like "SR-A-*", "Test", IIf(txtname Like "SR-*-A-*", "Test2", "Not Tested"))

    Thanks for the help!

  6. #6
    Join Date
    Jun 2004
    Posts
    92
    The function that you posted would only work in the debug console. This is the correct one which will work with EXPR1 : area([material])

    Code:
    Function Area(MATERIAL As String) As String
    
    Select Case True
        Case MATERIAL Like "SR-A-*"
            Area = "TEST"
        Case MATERIAL Like "SR-*-A-*"
            Area = "TEST2"
        Case Else
            Area = MATERIAL
    End Select
    End Function
    Additionally, having MATERIAL = "SR-A-23" in your function would defeat the purpose of it all since everytime it would run you would get TEST regardless of what you used to run the function.
    Last edited by sionus; 08-25-04 at 18:16.

  7. #7
    Join Date
    Jul 2003
    Posts
    292

    Thumbs up

    AWESOME!

    This works to!

    Thanks so much for everyone's help

  8. #8
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by sionus
    The function that you posted would only work in the debug console.
    Good catch sionus, I was not being specific for the implimentation just showing the functionality in the example.


    I have more data then the mention aboved. so will this work for different samples?
    If you have many codes for areas that can potentially change or require updates, you may want to create a locations table and use a Select statement to return the area. This would be easier to manage for the long term to prevent a code update just to add an area.

    Code:
    ' 1. Create a table with material code and area fields "tblArea"
    '  Add a record for each code area
    '  use wild cards to work with "Like" statement
    
    'tblArea
    ' Material|Area
    ' ------------------
    ' SR-A-* | Test1
    ' SR-*-A-* | Test2
    
    '2. create funtion with DLookup function to find the code/area from the table
    
    Function Area(strMaterial As String)
    
        retLoc = DLookup("[Area]", "tblArea", "'" & strMaterial & "' Like [Material]")
    
        Area = Nz(retLoc, "Location not in table")
    
    End Function 
    ~

    Bill

Posting Permissions

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