Results 1 to 14 of 14

Thread: Select Case

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi All,
    I have a "Select Case" method in my DB and I want to look for anything containing a name. I am trying to do this:

    Select Case strName
    Case "*anyname*"
    me.command123.visible = true
    Case Else
    me.command123.visible = false
    End Select

    When I run the code, it does not catch the name, even though it contains what would be "anyname" above. What am I doing wrong???

    Thanks,
    Mark

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by santosm View Post
    Hi All,
    I have a "Select Case" method in my DB and I want to look for anything containing a name. I am trying to do this:

    Select Case strName
    Case "*anyname*"
    me.command123.visible = true
    Case Else
    me.command123.visible = false
    End Select

    When I run the code, it does not catch the name, even though it contains what would be "anyname" above. What am I doing wrong???

    Thanks,
    Mark
    I don't understand what you want to do?

    Would you be more explicit.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by santosm View Post
    Hi All,
    I have a "Select Case" method in my DB and I want to look for anything containing a name. I am trying to do this:

    Select Case strName
    Case "*anyname*"
    me.command123.visible = true
    Case Else
    me.command123.visible = false
    End Select

    When I run the code, it does not catch the name, even though it contains what would be "anyname" above. What am I doing wrong???

    Thanks,
    Mark

    Do you mean, if there is anything in the strName variable ?

    If that is the case then this ought to work

    Code:
    Select Case Len(strName)
       Case 0
       	command123.visible = False
       Case Else
       	command123.visible = True
    End Select

    If not you need to clarify what is likely to be in strName, and what you are looking to check

    If you are only looking for one of 2 options though, you could just as easily use an IF


    Code:
    IF Len(strName) =0 Then
       	command123.visible = False
    Else
       	command123.visible = True
    End If

    Indeed in Both cases you could possibly just check for strName=""
    Andrew

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew and Pat,
    What I am looking for is to filter based on a name using wildcards. The name might be something like Acme North, Acme South, Acme West, etc. I tried using:

    case "*Acme*" but it wouldn't find any of those names.

    Thanks,
    Mark

    Quote Originally Posted by AndrewKKWalker View Post
    Do you mean, if there is anything in the strName variable ?

    If that is the case then this ought to work

    Code:
    Select Case Len(strName)
       Case 0
       	command123.visible = False
       Case Else
       	command123.visible = True
    End Select

    If not you need to clarify what is likely to be in strName, and what you are looking to check

    If you are only looking for one of 2 options though, you could just as easily use an IF


    Code:
    IF Len(strName) =0 Then
       	command123.visible = False
    Else
       	command123.visible = True
    End If

    Indeed in Both cases you could possibly just check for strName=""

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Mark, is this a Select Case in VBA, or is it a SELECT CASE in T-SQL (SQL Server or equivalent)? The replies have assumed it was in VBA, but from your code, it could well be a view or query in SQL Server.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't use wildcards in Select Case ... End Case.

    Try

    Code:
    If strName Like "*anyname*" Then
      Me.Command123.Visible = True
    Else
      Me.Command123.Visible = False
    End If
    or even

    Me.Command123.Visible = (strName Like "*anyname*")

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell,
    VBA, but now I see that Hans has said I cannot use wildcards in a select method.

    Thanks,
    Mark

    Quote Originally Posted by WendellB View Post
    Mark, is this a Select Case in VBA, or is it a SELECT CASE in T-SQL (SQL Server or equivalent)? The replies have assumed it was in VBA, but from your code, it could well be a view or query in SQL Server.

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,
    Thanks!

    Mark

    Quote Originally Posted by HansV View Post
    You can't use wildcards in Select Case ... End Case.

    Try

    Code:
    If strName Like "*anyname*" Then
      Me.Command123.Visible = True
    Else
      Me.Command123.Visible = False
    End If
    or even

    Me.Command123.Visible = (strName Like "*anyname*")

  9. #9
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Just in case you ever have a more complicated (more than two-choice) scenario where you'd prefer to use Select Case, note that you can use wildcards within Select Case if you structure it like this:

    Code:
        Dim strX As String
        strX = "porcupine"
        Select Case True
            Case strX Like "porco*"
                MsgBox "o"
            Case strX Like "porcu*"
                MsgBox "u"
            Case Else
                MsgBox "Nope"
        End Select

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks!

    Quote Originally Posted by st3333ve View Post
    Just in case you ever have a more complicated (more than two-choice) scenario where you'd prefer to use Select Case, note that you can use wildcards within Select Case if you structure it like this:

    Code:
        Dim strX As String
        strX = "porcupine"
        Select Case True
            Case strX Like "porco*"
                MsgBox "o"
            Case strX Like "porcu*"
                MsgBox "u"
            Case Else
                MsgBox "Nope"
        End Select

  11. #11
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a "Select Case" method in my DB and I want to look for anything containing a name. I am trying to do this:

    Select Case strName
    Case "*anyname*"
    me.command123.visible = true
    Case Else
    me.command123.visible = false
    End Select

    When I run the code, it does not catch the name, even though it contains what would be "anyname" above. What am I doing wrong???
    This soulds like a candidate for a regular expressions library!
    VBScript has such a library, and you can include the Scripting object inyour Access application.
    Regular Expression Library

  12. #12
    New Lounger
    Join Date
    Dec 2009
    Location
    Orange County, CA, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What have you set strName equal to? The variable needs it's data from somewhere.

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    strName already has a value applied to it from the form, in this case a company name.

    Thanks,
    Mark

    Quote Originally Posted by Mike Geer View Post
    What have you set strName equal to? The variable needs it's data from somewhere.

  14. #14
    New Lounger
    Join Date
    Dec 2009
    Location
    Orange County, CA, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by santosm View Post
    Hi,
    strName already has a value applied to it from the form, in this case a company name.

    Thanks,
    Mark

    Try using "" instead of "*anyname*".

Posting Permissions

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