Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2011
    Posts
    66

    Unanswered: split a string in table into multiple fields based on the occurrence of an underscore

    split a string into multiple fields based on the occurrence of an underscore.

    i have a string field contains as

    Example of the field content is:

    " stov_mica_tc_1050132_2201547.jpg"

    I need to split this field into multiple fields based on underscore such as:

    Field1: stov
    Field2: mica
    Field3: tc
    Field4: 1050132
    Field5: 2201547

    i have searched a code in with it can seperate into two fields

    Function ParseFirstComp(pValue) As String

    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = InStr(pValue, "_")

    'Return the portion of the string before the underscore
    If LPosition > 0 Then
    ParseFirstComp = Left(pValue, LPosition - 1)
    Else
    ParseFirstComp = ""
    End If

    End Function


    Function ParseSecondComp(pValue) As String

    Dim LPosition As Integer

    'Find postion of underscore
    LPosition = InStr(pValue, "_")

    'Return the portion of the string after the underscore
    If LPosition > 0 Then
    ParseSecondComp = Mid(pValue, LPosition + 1)
    Else
    ParseSecondComp = ""
    End If

    End Function

    but how to make for multiple fields : pls help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try something like (aircode):
    Code:
    Const c_SQL As String = "INSERT INTO DestinationTableName ( Field1, Field2, Field3, Field4, Field5 ) " & _
                            "VALUES ( '@', '@', '@', '@', '@' );"
    
    Dim rst As DAO.RecordSet
    Dim Var As Variant
    Dim str As String
    Dim strSQL as String
    Dim i As Long
    
    Set rst = CurrentDb.OpenRecordSet("SourceTableName", dbOpenSnapShot)
    With rst
        Do Until .eof
            Str = Left(!SourceFieldName, Instr(!SourceFieldName, ".")-1)
            Var = Split(str, "_")
            strSQL = Replace(c_SQL, "@", Var(0),, 1)
            For i = 1 to 4
                strSQL = Replace(strSQL, "@", Var(i),, 1)
            Next i
            CurrentDb.Execute strSQL, dbFailOnError
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You don't really say, but I assume that the 'fields' are Controls on a Form.

    Code:
    Components = Split(Left(Me.TargetField, InStr(Me.TargetField, ".") - 1), "_")
    
    Me.Field1 = Components(0)
    Me.Field2 = Components(1)
    Me.Field3 = Components(2)
    Me.Field4 = Components(3)
    Me.Field5 = Components(4)


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2011
    Posts
    66

    Thanks for reply

    SINNDHO THANK YOU VERY MUCH

    I have attached the database. i tried but not able to do

    can you pls help... pls

    missinglinq i need to put it in query and connect it to form
    for further use
    thanks for help u r right
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is the corrected procedure. Notice that:
    1. The contents of each FILENAME field in the table FILESNAME can be splitted into three (3) elements, not five, using the underscore character (_) as the separator. I added a test that makes the procedure able to cope with a variable number of elements.
    2. As the splitted elements must be stored into the same table as the source string, the SQL statement becomes an UPDATE instead of an INSERT.
    3. You can never have any VBA procedure outside of a Sub, Property or Function block.
    Code:
    Public Function SplitPath()
    
        Const c_SQL As String = "UPDATE FILESNAMES SET Field1 = '@', Field2 = '@', Field3 = '@', Field4 = '@', Field5 = '@';"
        
        Dim rst As DAO.Recordset
        Dim Var As Variant
        Dim str As String
        Dim strSQL As String
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordSet("FILESNAMES", dbOpenSnapShot)
        With rst
            Do Until .EOF
                str = Left(!FileName, InStrRev(!FileName, ".") - 1)
                Var = Split(str, "_")
                strSQL = Replace(c_SQL, "@", Var(0), , 1)
                For i = 1 To 5
                    If i > UBound(Var) Then
                        strSQL = Replace(strSQL, "@", "", , 1)
                    Else
                        strSQL = Replace(strSQL, "@", Var(i), , 1)
                    End If
                Next i
                CurrentDb.Execute strSQL, dbFailOnError
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    
    End Function
    Have a nice day!

  6. #6
    Join Date
    Sep 2011
    Posts
    66

    thanks for help sinndho

    thanks for help and sorry to trouble you

    can you pls tell me where i need to put the code and how i will follow that pls.

    as i am not able to get it

    i have created a table of your name and fields in your name.

    i have used 5 fields because the file name may some times have 3 to 5 underscores.

    if the name has five underscores then five fields will update and if 3 underscores only 3 fields will update

    pls help
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by eddi View Post
    can you pls tell me where i need to put the code and how i will follow that pls.
    Simply paste the code into a standard module. You can call it from any VBA procedure.

    Quote Originally Posted by eddi View Post
    i have used 5 fields because the file name may some times have 3 to 5 underscores
    if the name has five underscores then five fields will update and if 3 underscores only 3 fields will update
    .
    No problem, the function I provided can handle that.

    Note: The function uses the table FILESNAME. If you want to change that, modify it accordingly.
    Have a nice day!

  8. #8
    Join Date
    Sep 2011
    Posts
    66

    thanks sinndho but i am still hanging

    thanks for patience and help sinndho

    i have pasted the code in module1

    and created a form and put a command button in a form and called it by using


    Private Sub Toggle0_Click()
    Call SplitPath
    End Sub


    i am getting compile error pls

    bear with me & help
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the error message is?
    on what line?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2011
    Posts
    66

    thanks headlem

    sorry typed name wrongly healdem sorry

    its in here


    Public Function SplitPath() [ this is yellow color]

    Const c_SQL As String = "UPDATE FILESNAMES SET Field1 = '@', Field2 = '@', Field3 = '@', Field4 = '@', Field5 = '@';"

    Dim rst As DAO.Recordset [ this is highlighted in blue color]
    Dim Var As Variant
    Dim str As String
    Dim strSQL As String
    Dim i As Long
    Last edited by eddi; 10-01-13 at 11:50.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by eddi View Post
    Dim rst As DAO.Recordset [ this is highlighted in blue color]
    Check that your project has a reference to the Microsoft DAO 3.6 library (Tools --> References...).
    Have a nice day!

  12. #12
    Join Date
    Sep 2011
    Posts
    66

    thanks sinndho its working i updated library as u told

    thanks sinndho its working.. i selected library as u told

    but i am able to get data for 3 underscores but if i give 5 its not happening

    pls guide thanks for patience and help

    and one more doubt if i need to insert this in a query can i use your this code

    Const c_SQL As String = "INSERT INTO DestinationTableName ( Field1, Field2, Field3, Field4, Field5 ) " & _
    "VALUES ( '@', '@', '@', '@', '@' );"

    Dim rst As DAO.RecordSet
    Dim Var As Variant
    Dim str As String
    Dim strSQL as String
    Dim i As Long

    Set rst = CurrentDb.OpenRecordSet("SourceTableName", dbOpenSnapShot)
    With rst
    Do Until .eof
    Str = Left(!SourceFieldName, Instr(!SourceFieldName, ".")-1)
    Var = Split(str, "_")
    strSQL = Replace(c_SQL, "@", Var(0),, 1)
    For i = 1 to 4
    strSQL = Replace(strSQL, "@", Var(i),, 1)
    Next i
    CurrentDb.Execute strSQL, dbFailOnError
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Used as it is here, the function Split() can handle any number of elements. The only reason that should prevent the procedure from working with more than 3 elements I can imagine, would be that there are more than one dot (.) in the original string. You can change:
    Code:
    Str = Left(!SourceFieldName, Instr(!SourceFieldName, ".")-1)
    to:
    Code:
    Str = Left(!SourceFieldName, InstrRev(!SourceFieldName, ".")-1)
    and see if it makes any difference. You can also verify the number of elements after splitting the original string:
    Code:
    Str = Left(!SourceFieldName, Instr(!SourceFieldName, ".")-1)
    Var = Split(str, "_")
    MsgBox "Number of elements: " & Ubound(Var), vbInformation
    Have a nice day!

  14. #14
    Join Date
    Sep 2011
    Posts
    66

    thanks sinndho

    as you said there are more (.) dots in the name like its IP address i cannot change

    \\192.168.16.63\d\INSPECTION LAB\WORLD OF MATERIALS\CHEMICALS\FILE TC\NIPPON\WASHING_THINNER_SB_ COA _12100274.pdf

    is there any other method

    and one more thing

    in my other computer i have completely installed office 2003
    but i am not able to get the library. do i have any other option or change the code so that it will work

    Dim rst As DAO.Recordset

    pls suggest as i am not able to try the new code given by you

  15. #15
    Join Date
    Sep 2011
    Posts
    66

    sinndho code changed but not working

    sinndho i have changed the code but it not working as i wanted

    pls it is only filling the first ( field ) other fileds are empty even though there are more than 3 _ ( underscores

    pls look into this
    Attached Files Attached Files

Posting Permissions

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