Select case function

[mor]

Can anyone help me understand why when I run the code, VBA always skips to the last Case Else scenario, regardless of if there is a hash at the beginning, the end or both of the control Me.Liste_documentation.column(3).


Dim PathName As String, RemoveHashL As String, RemoveHashR As String
Dim Length As Long

Debug.Print Me.Liste_Documentation.Column(3)


Dim Crit As String
Crit = "#"

Debug.Print Left(Me.Liste_Documentation.Column(3), 1) 'Returns #
Debug.Print Right(Me.Liste_Documentation.Column(3), 1) 'Returns #

Select Case Crit

Case 1: Left(Me.Liste_Documentation.Column(3), 1) = Crit And _
Right(Me.Liste_Documentation.Column(3), 1) = Crit
' Effacer les # qui sont au début et en bout du chemin
Length = Len(Me.Liste_Documentation.Column(3)) - 1
RemoveHashR = Left(Me.Liste_Documentation.Column(3), Length)
Length = Length - 1
RemoveHashL = Right(RemoveHashR, Length)
' Chemin
PathName = RemoveHashL

Case 2: Left(Me.Liste_Documentation.Column(3), 1) = Crit
' Effacer le # qui au début du chemin
Length = Len(Me.Liste_Documentation.Column(3)) - 1
RemoveHashR = Left(Me.Liste_Documentation.Column(3), Length)
' Chemin
PathName = RemoveHashR

Case 3: Right(Me.Liste_Documentation.Column(3), 1) = Crit
Length = Len(Me.Liste_Documentation.Column(3)) - 1
RemoveHashL = Right(Me.Liste_Documentation.Column(3), Length)
' Chemin
PathName = RemoveHashL

Case Else: MsgBox ("The following path: " & Me.Liste_Documentation.Column(3) _
& " was not recognised")

End Select


Thanks for any help
MOR

 

[pr2-eugin]

mor, not sure what you are trying to do at this line..

    Case 1
        Left(Me.Liste_Documentation.Column(3), 1) = Crit And _
        Right(Me.Liste_Documentation.Column(3), 1) = Crit

The problem for your code not working as expected is because you are assigning crit as #

Crit = "#"

Select Case Crit

which is not defined in the Case, so it goes to the Else.

 

[mor]

Basically what I want to do is to:

Case1: The first character in the string = # and the last character in the string = #

Delete both #

Case 2: First character in the string = #

Delete the # at the beginning

Case 3: Last character in the string = #

Delete the # at the end.

So I need to set the criteria to set the criteria as a hash no?

 

[pr2-eugin]

Could a Replace function not serve you the purpose?

PathName = [URL="http://www.techonthenet.com/access/functions/string/replace.php"]Replace[/URL](Me.Liste_Documentation.Column(3), "#", "")

Example is..

? Replace("#Hello World#", "#", "")
Hello World
? Replace("#Hello World", "#", "")
Hello World
? Replace("Hello World#", "#", "")
Hello World

PS: Please use Code Tags when posting VBA Code

 

[missinglinq]

Just to reinforce what pr2-eugin said, with

Crit = "#"

Select Case Crit

Cases 1, 2 or 3 will never fire! They would only fire if

Crit = 1, Crit = 2 or Crit = 3.

If your object is to replace all instances of the Octothorp (#), his example using Replace() is the way to go.

Linq ;0)>