extract text from string to use in other columns

[zorcy]

I have an Excel macro I wrote in VBA that will take carriage return separated lines in a cell, isolate one line by use of key word, pick the remainder of the line by character count and move that text to another column. Is there a way to do the same in Access?



Here is a typical looking cell:


Sequence No: ..#074
Date: .........01-May-10
Time: ......... 23:35:20.468
Source: .......AHU12_04_SA_Temperature


Description: ..AHU Supply Air Temperature
Condition: ....HiRange
Priority: .....U 08
Value: ........24.180000
Units: ........
Message: ......Too Hot

We are pulling out the line starting Source: and keeping anything after the ".".

Here is the code I have used in Excel:

Sub Report2()
   '\\ Run second this will pull out the sub product from the description column
    Dim s() As String
    Dim iTemp As Integer
    Dim r As Excel.Range
     
    For Each r In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
         
        s = Split(r.Value, Chr$(10))
         
        For iTemp = 0 To UBound(s)
             
             
            If Left$(s(iTemp), 7) = "Source:" Then
                r.Offset(0, -3).Value = Replace(Replace(s(iTemp), "Source:", vbNullString), ".", vbNullString)
                 
    
            End If
        Next
        
    Next
     
End Sub

My experience is limited, read that null, but am learning.

[Beetle]

Given a field in an Access table or query with the following value;

Sequence No: ..#074
Date: .........01-May-10
Time: ......... 23:35:20.468
Source: .......AHU12_04_SA_Temperature

The following expression (which would typically be placed in a caclulated query field or form/report control);

Mid([TestField],InStr([TestField],"Source")+15)

will return

AHU12_04_SA_Temperature

However, that assumes that this part of the string

Source: .......AHU12_04_SA_Temperature

always has the word Source followed by 9 characters (a colon, a space and 7 dots). If this does not always hold true then it will need some modification.