Select Case

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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
[/Code]

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
[/Code]

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

Reply | Quote

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

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
[/Code]

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
[/Code]

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

Reply | Quote

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*”)

Reply | Quote

Hi Hans,
Thanks!

Mark

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*”)

Reply | Quote

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

Reply | Quote

Thanks!

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

Reply | Quote

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.

Reply | Quote

Hi Wendell,
VBA, but now I see that Hans has said I cannot use wildcards in a select method.

Thanks,
Mark

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.

Reply | Quote

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

Reply | Quote

What have you set strName equal to? The variable needs it’s data from somewhere.

Reply | Quote

Hi,
strName already has a value applied to it from the form, in this case a company name.

Thanks,
Mark

What have you set strName equal to? The variable needs it’s data from somewhere.

Reply | Quote

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*”.

Reply | Quote