Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Code for dynamic connection of text-data from different columns

    Hi there,

    I have the following problem:

    Imagine the following columns of a table

    Connection
    A
    B
    C
    D
    ...
    Z
    Result

    The columns A to Z are for textstrings. In column "connection", there is a textstring which defines how to connect the records in columns A, B, C, ... with single letters. Now, I would need a code (VBA, SQL) which produces this new string according to the definition in column "connection" and writes it in column "result". Please have a look at the attached picture with an example, this is much easier to understand than my explanation...

    Thank you very much for your help in advance,

    Josef
    Attached Thumbnails Attached Thumbnails example.JPG  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Option Compare Binary
    Option Explicit
    
    Sub Parser()
    
        Dim rst As DAO.Recordset
        Dim strResult As String
        Dim strChar As String
        Dim i As Long
        
        Set rst = CurrentDb.OpenRecordset("SomeTable", dbOpenDynaset)
        With rst
            Do Until .EOF
                strResult = ""
                For i = 1 To Len(!Connection)
                    strChar = Mid(!Connection, i, 1)
                    Select Case strChar
                        Case "A" To "Z":    strResult = strResult & .Fields(strChar).Value
                        Case Else:          strResult = strResult & strChar
                    End Select
                Next i
                .Edit
                !Result = strResult
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    The code must be in a module having the directive Option Compare Binary. Otherwise the parser won't be able to distinguish between "X" and "x".
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    2
    Hi Sinndho,

    I'm impressed, it's exactly what I was searching for and it works! Thanks a lot for this super fast and nice solution!

    Best regards,

    Josef

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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