comma delimit an access text field

Use VBA.
If you need an example on how to do it Email me @ newyorker211@yahoo.com

Do this with append queries.

First query Grab ID and Field1, make it a make table query so that the new table can be created. Call your fields ID and FIELDA (or whatever you want to call it)

Secind Query will be an append query, Grab ID and Field2, these fields will be appended to ID and FIELDA

third querywill append ID and Field3 to ID and FIELDA.

Hope this helps

Hello mandajoy,

Use following sub-procedure to achieve the table conversion you desire.
It is based on Access 2000 with DAO 3.6 referenced. You may also run it in Access 97. (You may need to remove dao from dao.Recordset in 97 in the Dim statements)

It is assumed that the table 'OldTable' contains old data and 'NewTable' contains the formatted data.

Sub CommaDelimit()

Dim rsOldTable As DAO.Recordset
Dim rsNewTable As DAO.Recordset

Set rsOldTable = CurrentDb.OpenRecordset("OldTable")
Set rsNewTable = CurrentDb.OpenRecordset("NewTable")

Dim lngID As Long
Dim strFld1 As String
Dim strFld2 As String
Dim strFld3 As String
Dim strFld31 As String
Dim strFld32 As String
Dim strFld33 As String
While rsOldTable.EOF = False
lngID = rsOldTable.Fields(0)
strFld2 = rsOldTable.Fields(1)
strFld3 = rsOldTable.Fields(2)

Dim intLen31 As Integer
intLen31 = InStr(1, strFld3, ",")
strFld31 = Left(strFld3, intLen31 - 1)
With rsNewTable
.AddNew
.Fields(0) = (lngID - 1)* 3 + 1
.Fields(1) = strFld2
.Fields(2) = strFld31
.Update
Dim intLen32 As Integer
intLen32 = InStr(intLen31 + 1, strFld3, ",")
strFld32 = Mid(strFld3, intLen31 + 1, intLen32 - intLen31 - 1)
.AddNew
.Fields(0) = (lngID - 1) * 3 + 2
.Fields(1) = Trim(strFld2)
.Fields(2) = Trim(strFld32)
.Update
Dim intLen33 As Integer
intLen33 = Len(strFld3) - intLen32
strFld33 = Right(strFld3, intLen33)
.AddNew
.Fields(0) = (lngID - 1) * 3 + 3
.Fields(1) = Trim(strFld2)
.Fields(2) = Trim(strFld33)
.Update
End With
rsOldTable.MoveNext
Wend
Set rsOldTable = Nothing
Set rsNewTable = Nothing

End Sub

I have successfully tried this code and would be happy to help you successfully implement this code.

Your feedback would be highlyappreciated.

With best regards,

SanganakSakha,
Your Friend In Need.

This question was auto closed due to inactivity