General discussion

Locked

comma delimit an access text field

By mandajoy ·
How do you delimit an access text field so data that was once:

ID Field 1 Field 2
1 Gerry Baseball, Soccer, Swimming

To:

ID Field 1 Field 2
2 Gerry Baseball
3 Gerry Soccer
4 Gerry Swimming

any suggestions?

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

comma delimit an access text field

by shmaltz In reply to comma delimit an access t ...

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

Collapse -

comma delimit an access text field

by mandajoy In reply to comma delimit an access t ...

The question was auto-closed by TechRepublic

Collapse -

comma delimit an access text field

by nicholasP In reply to comma delimit an access t ...

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

Collapse -

comma delimit an access text field

by mandajoy In reply to comma delimit an access t ...

The question was auto-closed by TechRepublic

Collapse -

comma delimit an access text field

by SanKulPune In reply to comma delimit an access t ...

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.

Collapse -

comma delimit an access text field

by mandajoy In reply to comma delimit an access t ...

The question was auto-closed by TechRepublic

Collapse -

comma delimit an access text field

by mandajoy In reply to comma delimit an access t ...

This question was auto closed due to inactivity

Back to Software Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums