none
Re: Separating Left & Right strings in vba for Access 2007 RRS feed

  • Question

  • Hi there, As an  example, how would you separate  ABC-DXZ to form two separate words in Access using vba. I used this code for the first word, but didn't work. Any ideas please?

    lblfirstPart.Caption = Left(Me.SetUpType.Text, InStr(Me.SetUpType.Text, " - "))
    Any help would be greatly appreciated! Thanks!


    JMacabi



    • Edited by JwasWell Tuesday, November 24, 2015 5:55 PM
    Tuesday, November 24, 2015 5:47 PM

Answers

  • Hi Miriam

    Thanks for your reply.

    I tried coding this in Visual Basic 10 without using TRIM and it worked fine. The character is a dash. I tried copying and pasting as you suggested the character in to the delimiter argument of the split function and it worked! For some reason it needed the act of copying and pasting to recognize the character.

    Thanks again!

    Jonathan 




    • Edited by JwasWell Thursday, November 26, 2015 11:19 AM
    • Marked as answer by JwasWell Friday, November 27, 2015 9:51 AM
    Thursday, November 26, 2015 11:08 AM

All replies

  • For your example ABC-DXZ

    =Left(Me.SetUpType, 4)
    will return  ABC-

    = InStr(Me.SetUpType,"-")
    will return  4

    = Left(Me.SetUpType, InStr(Me.SetUpType,"-"))
    will return  ABC-

    = Left(Me.SetUpType, (InStr(Me.SetUpType, "-") - 1))
    will return  ABC

    = Mid(Me.SetUpType, 4)
    will return  -DXZ

    = Mid(Me.SetUpType, (InStr(Me.SetUpType, "-") + 1))
    will return  DXZ

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.



    Tuesday, November 24, 2015 6:46 PM
  • Hi. Since you asked, here's a purely VBA answer (it won't work in queries).

    In the Immediate Window...

    ?Split("ABC-DXZ","-")(0)
    ABC

    ?Split("ABC-DXZ","-")(1)
    DXZ

    Hope that helps...

    Tuesday, November 24, 2015 7:08 PM
  • Thanks for your reply.

    The solution you proposed still doesn't work. The problem is concerning  access reports. One field on my report is prepopulated already from the database. This field contains strings of the form ABC - DEF. Now these strings on either side could be of variant length. I need to view these two strings separately in say two different labels. So the solution would be say

    1st Label :   lblfirstPart.Caption = Left(Me.SetUpType.Text, InStr(Me.SetUpType.Text, ("-"))) 

    Now when I use the debugger and watch view for InStr(Me.SetUpType.Text, ("-")) the value returned is zero, which means nothing gets displayed. There is something wrong with the InStr(Me.SetUpType.Text, ("-")) part. I don't have a clue. Consequently I have found that if I use say 

       lblfirstPart.Caption = Left(Me.SetUpType.Text, 15)  that would work and separate part of the string, but not exactly the right part. Consequently the 2nd label would be something of the form

    2nd Label : lblSecondPart.Caption = Mid(InStr(Me.SetUpType.Text, ("-")), Len(Me.SetUpType.Text))

    Thanks again

    Jonathan


    JMacabi

    Wednesday, November 25, 2015 9:57 AM
  • Thanks for your reply.

    The solution you proposed still doesn't work. The problem is concerning  access reports. One field on my report is prepopulated already from the database. This field contains strings of the form ABC - DEF. Now these strings on either side could be of variant length with spaces and brackets. I need to view these two strings separately in say two different labels. So the solution would be say

    1st Label :   lblfirstPart.Caption = Left(Me.SetUpType.Text, InStr(Me.SetUpType.Text, ("-"))) 

    Now when I use the debugger and watch view for InStr(Me.SetUpType.Text, ("-")) the value returned is zero, which means nothing gets displayed. There is something wrong with the InStr(Me.SetUpType.Text, ("-")) part. I don't have a clue. Consequently I have found that if I use say 

       lblfirstPart.Caption = Left(Me.SetUpType.Text, 15)  that would work and separate part of the string, but not exactly the right part. Consequently the 2nd label would be something of the form

    2nd Label : lblSecondPart.Caption = Mid(InStr(Me.SetUpType.Text, ("-")), Len(Me.SetUpType.Text))

    When I used the Split method proposed by yourself on the field say lblfirstPart.Caption = Split(Me.SetUpType.Text, "-")(0) It produced the whole field ABC - DEF (including the information after the dash)

    Thanks again for your kind feedback and efforts.


    JMacabi

    Wednesday, November 25, 2015 10:07 AM
  • Those functions *should work.  For example, testing in the immediate window I get the following:

    Typed:   s = "ABC - DEF"

                 ? Split(s,"-")(0)

    Returned:   ABC

    Typed:   ? Split(s,"-")(1)

    Returned:  DEF

    You may want to use the TRIM function to get rid of extraneous spaces.

    But if you're seeing the full string in your results, is that character perhaps an underscore and not a dash?

    Try copy/pasting the character in to the delimiter argument of the split function...

    


    Miriam Bizup Access MVP

    Wednesday, November 25, 2015 12:35 PM
  • Now these strings on either side could be of variant length with spaces and brackets.
    If you want to extract the substrings devoid of any spaces, parentheses or other non-alphabetic characters a couple of simple functions should do it:

    Public Function GetLeft(strText As String) As String

        Dim n As Integer
        Dim strSub As String
        
        For n = 1 To Len(strText)
            Select Case UCase(Mid(strText, n, 1))
                Case "A" To "Z"
                strSub = strSub & Mid(strText, n, 1)
                Case "-"
                Exit For
            End Select
        Next n
        
        GetLeft = Trim(strSub)

    End Function

    Public Function GetRight(strText As String)

        Dim n As Integer
        Dim strSub As String
        
        For n = Len(strText) To 1 Step -1
            Select Case UCase(Mid(strText, n, 1))
                Case "A" To "Z"
                strSub = Mid(strText, n, 1) & strSub
                Case "-"
                Exit For
            End Select
        Next n
        
        GetRight = Trim(strSub)

    End Function


    You can test them like this:

        Dim SetUpType As String

        SetUpType = "ABC-DFG"
        Debug.Print "1. " & GetLeft(SetUpType)
        
         SetUpType = "ABC - DFG"
        Debug.Print "2. " & GetLeft(SetUpType)
       
            SetUpType = "(ABC) - (DFG)"
        Debug.Print "3. " & GetLeft(SetUpType)

    Which returns:

    1. ABC
    2. ABC
    3. ABC

    and:

        Dim SetUpType As String

        SetUpType = "ABC-DFG"
        Debug.Print "1. " & GetRight(SetUpType)
        
         SetUpType = "ABC - DFG"
        Debug.Print "2. " & GetRight(SetUpType)
       
            SetUpType = "(ABC) - (DFG)"
        Debug.Print "3. " & GetRight(SetUpType)

    Which returns:

    1. DFG
    2. DFG
    3. DFG

    If you want other characters returned, e.g. numbers within each substring, it's a simple task to amend the Select Case construct in each function.

    BTW, don't call the Text property; it requires the control to have focus.  Just reference the control by name.  This returns the default Value property.


    Ken Sheridan, Stafford, England

    Wednesday, November 25, 2015 1:04 PM
  • JMacabi since you said;
    “The problem is concerning access reports.”
    I’ll ask two questions:  
    Does this report use a table or query as the “Record Source”?
    Is  lblfirstPart and lblSecondPart in a Header section of the report or Detail section?

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, November 25, 2015 2:20 PM
  • Hi Miriam

    Thanks for your reply.

    I tried coding this in Visual Basic 10 without using TRIM and it worked fine. The character is a dash. I tried copying and pasting as you suggested the character in to the delimiter argument of the split function and it worked! For some reason it needed the act of copying and pasting to recognize the character.

    Thanks again!

    Jonathan 




    • Edited by JwasWell Thursday, November 26, 2015 11:19 AM
    • Marked as answer by JwasWell Friday, November 27, 2015 9:51 AM
    Thursday, November 26, 2015 11:08 AM
  • Hi Ken

    Thanks for your reply and suggestions.

    I tried coding this in Visual Basic 10 without using TRIM and it worked fine. The character is a dash. I tried copying and pasting as Miriam suggested in an earlier response, the character in to the delimiter argument of the split function and it worked! For some reason it needed the act of copying and pasting to recognize the character.

    Thanks again for your response!

    Jonathan 




    • Edited by JwasWell Thursday, November 26, 2015 11:18 AM
    Thursday, November 26, 2015 11:12 AM
  • Hi h2fcell

    Thanks for your reply and suggestions.

    I tried coding this in Visual Basic 10 without using TRIM and it worked fine. The character is a dash. I tried copying and pasting as Miriam suggested in an earlier response, the character in to the delimiter argument of the split function and it worked! For some reason it needed the act of copying and pasting to recognize the character.

    Thanks again for your response!

    Jonathan 



    • Edited by JwasWell Thursday, November 26, 2015 11:18 AM
    Thursday, November 26, 2015 11:14 AM
  • Hi JwasWell,

    I am glad your issue has been resolved with copying and pasting. I suggest you mark the helpful replay as answer to close this thread, and then others who run into the same issue could find the answer easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 27, 2015 3:04 AM
  • Hi Edward,

    I have marked the helpful reply as an answer, for future use.

    I would like to thank you again and the participants of the forum. This forum is a great educational resource to further ones understanding of any of the Microsoft products.

    Best Regards

    Jonathan 



    • Edited by JwasWell Friday, November 27, 2015 10:00 AM
    Friday, November 27, 2015 10:00 AM
  • Hi Jonathan. Good luck with your project.
    Friday, November 27, 2015 4:07 PM
  • Hi the DB guy,

    Thank you!

    Jonathan


    • Edited by JwasWell Sunday, November 29, 2015 9:32 AM
    Sunday, November 29, 2015 9:32 AM