Read Text field line by line

[pekajo]

Hi,

I have a text field that looks like
John Smith
34 Black Street
Liverpool
Queensland

I need some VBA to read the text, line by line, as I have to insert each line into other fields.
Appreciate any help
Peter

 

[PeterF]

If it is a onetime function:

Function SplitMultilineTextField()
    Dim RS As DAO.Recordset
    Dim MultilineString As Variant
    Dim i As Integer
    Set RS = CurrentDb.OpenRecordset("Select * from [COLOR="DarkRed"]YourtableName[/COLOR];")
    
    While Not RS.EOF
        MultilineString = Split(Nz(RS![COLOR="darkred"]YourFieldName[/COLOR], ""), vbNewLine)
        
        For i = 0 To UBound(MultilineString)
            Debug.Print "field" & i + 1 & " is " & MultilineString(i)
        Next
[COLOR="Green"]            'To add to other fields in this recordset something like:
            RS!YourNewFieldNAme1 = MultilineString(0)
            RS!YourNewFieldNAme2 = MultilineString(1)
            RS!YourNewFieldNAme3 = MultilineString(2)
            RS!YourNewFieldNAme4 = MultilineString(3)
            'this will error if there are less then 4 lines
[/COLOR]
        RS.MoveNext
    Wend
End Function

 

[smig]

try this code

dim strText as string
 
strText = Split(strTextField, vbLF)
Field1 = strText (0)
Field2 = strText (1)
Field3 = strText (2)
...

 

[vbaInet]

PeterF gives a more detailed version though, and I would be inclined to use vbNewline (like PeterF's example) instead of vbLF.

 

[pekajo]

Hi,

Thanks to you all

Regards
Peter