case statement not working

[pb21]

I have the following code that I hoped would use the left function to determine if a part of the string contained another string and then take the appropriate case but it doesnt work.
product is the full description

select case product

Case Left(Product, 29) = "Summer Individual Residential"

Case Left(Product, 25) = "Summer Homestay Intensive"

end select

a watch shows that product is ok so if product is "Summer Individual Residential Royal Russell" I had hoped it would select the first case but it doest.

help would be much appreciated.

regards

peter

 

[Mile-O]

That's not how a Select Case structure is used.

The idea is that you are evaluating multiple possibilities of one value. i.e.

Select Case Me.txtProduct
    Case Is = "Product A"
        strProduct = "A"
    Case Is = "Product B"
        strProduct = "B"
    Case Is = "Product C"
        strProduct = "C"
    Case Is = "Product D"
        strProduct = "D"
    Case Else
        strProduct = "X"
End Select

As you can see, you can't perform a function in the case section - it is only evaluating the possibilities of criteria against txtProduct.

For you, the following shoudl work:

Select case product
    Case Like "Summer Individual Residential*"
    Case Like "Summer Homestay Intensive*"
    Case Else
End Select

 

[pb21]

case problem

I like your solution however it just highlights in red:

Select Case Product
Case like "Summer Groups Residential*"

end select

this would be the ideal solution though any other ideas?

regards

 

[Mile-O]

In that case you are going to have to use an IF...THEN...ELSEIF...ELSE...END IF construct.

If Left$(Product, 29) = "Summer Individual Residential" Then

ElseIf Left$(Product, 25) = "Summer Homestay Intensive"

Else

End If

Ideally, I think I can see what you are doing. Do these products not have an ID field? Personally, I'd used that and the Select Case structure would be fine.

 

[pb21]

case

I was thinking about that route but unfortunately they were created at different times so where:
case 1 to 10

case 11 to 20

...

would have done the trick now it would mean an individual case for each one. I suppose I could copy the groups to a new table and then rename it.

BUT thank you so much especially on a bank holiday weekend.

regards
peter

 

[ChrisO]

G’day Peter.

Try flipping the logic…why you would want to do this, apart from the fact it reads well, I do not know.

Option Explicit
Option Compare Text


Sub Test()
    Dim Product As String

    [color=green]'   Test case one.[/color]
    Product = "Summer Individual Residential Royal Russell"

    Select Case True
    
        Case Left$(Product, 29) = "Summer Individual Residential"
            MsgBox Product
        
        Case Left$(Product, 25) = "Summer Homestay Intensive"
            MsgBox Product
        
        Case Else
            MsgBox "Not in this Product."
            
    End Select



    [color=green]'   Test case two.[/color]
    Product = "Summer Homestay Intensive Royal Russell"

    Select Case True
    
        Case Left$(Product, 29) = "Summer Individual Residential"
            MsgBox Product
        
        Case Left$(Product, 25) = "Summer Homestay Intensive"
            MsgBox Product
        
        Case Else
            MsgBox "Not in this Product."
            
    End Select

End Sub

Hope that helps.

Regards,
Chris.

 

[izyrider]

?? try padding the string:

select case left$(Product & string(30, "§"), 30)
case "Summer Individual Residential§"
'blah blah
case "Summer Homestay Intensive§§§§§"
'blah blah
end select

izy



later thoughts: nope - that wont work with trailing text after Residential.

if it is always the first three words then you can do something similar using a function that returns the first three words and pad that.

here's something that will return "Fred Smith " from "Fred Smith and his brother". note that my function adds a trailing space (needed by my app)

just need to modify it to collect three words instead of two:

Public Function GetName(strIn As String) As String
    'ACCEPTS
    '   an input string containing zero or more characters
    'RETURNS
    '           "" on error
    '               else the first two words if strIn contains two or more words
    '                   else the first one word if strIn contains any text
    '                       else "EMPTY " if strIn is empty
    '           a word is defined as any non-space text followed by a space or end-of-text
    Dim strTmp As String
    Dim isDone As Boolean
    Dim atChar As Integer
    On Error GoTo err_GetName
    'handle empty input ------------------------------------------------------
    If Len(strIn) = 0 Then
        GetName = "EMPTY "
        Exit Function
    End If
    'eliminate any multiple-space issues --------------------------------------
    isDone = False
    Do Until isDone
        atChar = InStr(1, strTmp, "  ")
        If atChar = 0 Then
            isDone = True
        Else
            strTmp = Left$(strTmp, atChar) & Mid$(strTmp, atChar + 1)
        End If
    Loop
    'kill any leading/trailing spaces
    strTmp = strIn
    strTmp = Trim(strTmp)
    'handle single word only -------------------------------------------------
    If InStr(1, strTmp, " ") = 0 Then
        GetName = strTmp & " " 'add trailing space and we are done
        Exit Function
    End If
    'handle multiple words ---------------------------------------------------
    strTmp = strTmp & " "   'so we can find the second space in "Fred Smith"
    GetName = Left$(strTmp, InStr(InStr(1, strTmp, " ") + 1, strTmp, " "))
exit_GetName:
    Exit Function
err_GetName:
    GetName = ""
    MsgBox Err.Number & ": " & Err.Description, vbInformation, "Error in GetName()"
    Resume exit_GetName
End Function

(and yes - replace() might be a candidate for the multiple-space issue, but i didn't use it here - i forget why)

izy

 

[pb21]

case statement

Thanks to everyone who contributed especially as it was a bank holiday weekend, Your assistance helped me reach the solution.

thank you so much.

regards
Peter