none
Capture some characters after or between a slash or special character RRS feed

  • Question

  • I have a number in my forms that show the insurance policy number. It is like for example 3121520/95/124. Because the number of digits are not equal in some parts (between slashes) I cannot use Mid function to capture them. The second part have a fixed number of digits (2 digits) but the third part sometimes has 1,2 or 3 characters. I want to capture second part, and digits of third part. Do we have a function that I can use it to capture the digits or character after for example a slash character or _ character.

    First Part/Second Part/Third Part

    Examples:

    3121520/95/1-------- I want 95 And 1

    4221520/94/25

    5321520/92/1001

    I want digits in second and third parts.


    Karim Vaziri Regards,

    Friday, January 20, 2017 5:45 PM

Answers

  • Try something like

     

    Left(Mid([str],InStr([str],"/")+1),InStr(Mid([str],InStr([str],"/")+1),"/")-1)

    Replace [str] with your field name

     

    If you are certain the number will always be 2 digits then it could be simplified down to

    Left(Mid([str], InStr([str], "/") + 1), 2)

    Or

    Mid([str], InStr([str], "/") + 1, 2)

    Otherwise, use the first, more versatile, expression.

    Edit (add another option)

    Another option would be to create a public function to render the split function directly accessible in the query editor.  So you add a function: such as

    Function mySplit(sMyText As String, sDelim As String, lIndx As Long) As String
        On Error GoTo Error_Handler
    
        mySplit = Split(sMyText, sDelim)(lIndx)
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    
    Error_Handler:
        If Err.Number = 9 Then
            mySplit = ""
        Else
            MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: mySplit" & vbCrLf & _
                   "Error Description: " & Err.Description & _
                   Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
        End If
        Resume Error_Handler_Exit
    End Function

    and then, in your query you'd simply use

    mySplit([str],"/",1)

     

    Also, this should not be stored as a single string, but rather each element  (First Part/Second Part/Third Part) should be stored separately for proper normalization of the data.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net






    Friday, January 20, 2017 6:00 PM
  • In VBA, you can use the Split function:

        Dim arrParts() As String
        Dim SecondPart As String
        Dim ThirdPart As String
        arrParts = Split([MyField], "/")
        SecondPart = arrParts(1)
        ThirdPart = arrParts(2)

    MyField is the field/control containing the insurance policy number.

    In an expression in a query or in the control source of a text box, you can use

    Mid([MyField],InStr([MyField],"/")+1,InStrRev([MyField],"/")-InStr([MyField],"/")-1)

    and

    Mid([MyField],InStrRev([MyField],"/")+1)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kvaziri Sunday, January 22, 2017 6:49 PM
    Friday, January 20, 2017 6:01 PM

All replies

  • Try something like

     

    Left(Mid([str],InStr([str],"/")+1),InStr(Mid([str],InStr([str],"/")+1),"/")-1)

    Replace [str] with your field name

     

    If you are certain the number will always be 2 digits then it could be simplified down to

    Left(Mid([str], InStr([str], "/") + 1), 2)

    Or

    Mid([str], InStr([str], "/") + 1, 2)

    Otherwise, use the first, more versatile, expression.

    Edit (add another option)

    Another option would be to create a public function to render the split function directly accessible in the query editor.  So you add a function: such as

    Function mySplit(sMyText As String, sDelim As String, lIndx As Long) As String
        On Error GoTo Error_Handler
    
        mySplit = Split(sMyText, sDelim)(lIndx)
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Function
    
    Error_Handler:
        If Err.Number = 9 Then
            mySplit = ""
        Else
            MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: mySplit" & vbCrLf & _
                   "Error Description: " & Err.Description & _
                   Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
        End If
        Resume Error_Handler_Exit
    End Function

    and then, in your query you'd simply use

    mySplit([str],"/",1)

     

    Also, this should not be stored as a single string, but rather each element  (First Part/Second Part/Third Part) should be stored separately for proper normalization of the data.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net






    Friday, January 20, 2017 6:00 PM
  • In VBA, you can use the Split function:

        Dim arrParts() As String
        Dim SecondPart As String
        Dim ThirdPart As String
        arrParts = Split([MyField], "/")
        SecondPart = arrParts(1)
        ThirdPart = arrParts(2)

    MyField is the field/control containing the insurance policy number.

    In an expression in a query or in the control source of a text box, you can use

    Mid([MyField],InStr([MyField],"/")+1,InStrRev([MyField],"/")-InStr([MyField],"/")-1)

    and

    Mid([MyField],InStrRev([MyField],"/")+1)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by kvaziri Sunday, January 22, 2017 6:49 PM
    Friday, January 20, 2017 6:01 PM
  • Dear Hans,

    The code work perfect in query, but I don't know why your first code in VBA triggers an error. When I use it in VBA  I get an error like this:


    Karim Vaziri Regards,

    • Marked as answer by kvaziri Sunday, January 22, 2017 6:49 PM
    • Unmarked as answer by kvaziri Sunday, January 22, 2017 6:49 PM
    Friday, January 20, 2017 8:03 PM
  • I suspect that the spaces in the highlighted line were replaced by non-breaking spaces in the browser.

    Please select the spaces, delete them, then type them in again.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, January 20, 2017 9:30 PM