Unanswered: Code for dynamic connection of text-data from different columns
I have the following problem:
Imagine the following columns of a table
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...
Option Compare Binary
Dim rst As DAO.Recordset
Dim strResult As String
Dim strChar As String
Dim i As Long
Set rst = CurrentDb.OpenRecordset("SomeTable", dbOpenDynaset)
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
!Result = strResult
Set rst = Nothing
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".