How to do this?

[DawnTreader]

Hello All

i have 2 fields in a table. one is the id field the other is one where i
have cut and pasted from an excel sheet.

in the second field i have data that looks like this: 18106-1,-2

how do i change it to 2 seperate records that looks like this: 18106-1

18106-2

should i be using an append or update query or code? and if a query will do
this what does the sql look like?

thanks for any and all help

 

[DawnTreader]

dang that post got screwed up a little

in the second field i have data that looks like this:
ID Serial
1 18106-1,-2
2 18107-1,-2
etc

how do i change it to 2 seperate records that looks like this:
ID Serial
1 18106-1
2 18107-1
3 18106-2
4 18107-1

again all help appreciated

 

[John W. Vinson]

Hello All

i have 2 fields in a table. one is the id field the other is one where i
have cut and pasted from an excel sheet.

in the second field i have data that looks like this: 18106-1,-2

how do i change it to 2 seperate records that looks like this: 18106-1

18106-2

should i be using an append or update query or code? and if a query will do
this what does the sql look like?

thanks for any and all help

You'll need some VBA to do this, I would expect; and even at that, it will
depend on your Excel data being accurate and consistant (good luck on THAT
one). You can use the Split() function in VBA to read in the comma-separated
string, split it into its components, and reassemble them. Untested air code:

Dim strPieces() As String
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim iPos As Integer
Dim strID As String
Set rsIn = db.OpenRecordset("your table", dbOpenDynaset)
Set rsOut = db.OpenRecordset("your table", dbOpenDynaset)
Do Until rsIn.EOF
If InStr(rsIn!fieldname, ",") <> 0 Then
strPieces = Split(rsIn!fieldname, ",") ' get an array of the pieces
' the first piece will be "18601-1", the second piece "-2", etc.
rsOut.AddNew ' add a new record to the table
rsOut!fieldname = strPieces(0) ' output first record
rsOut.Update
strID = Left(strPieces(0), InStr(strPieces(0), "-")-1) ' e.g. "18601"
For iPos = 1 to strPieces.UBound - 1
rsOut.AddNew
rsOut!fieldname = strID & strPieces(iPos)
rsOut.Update
Next iPos
rsIn.MoveNext
Loop

 

[DawnTreader]

Hello John

thanks, will have to test it monday morning!

 

[DawnTreader]

Hello John

i have tried the code, it stops on the strPieces.UBound-1. says that there
is an invalid qualifier and highlights the "strPieces" variable. i looked up
the UBound and found that the UBound should be first with the array inside
brackets, tried this code:

Private Sub splitdispensers()
Dim strPieces() As String
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim iPos As Integer
Dim strID As String
Dim DB As Database

Set rsIn = DB.OpenRecordset("utblSerialList", dbOpenDynaset)
Set rsOut = DB.OpenRecordset("utblSerialList", dbOpenDynaset)
Do Until rsIn.EOF
If InStr(rsIn!Serial, ",") <> 0 Then
strPieces = Split(rsIn!Serial, ",") ' get an array of the pieces
' the first piece will be "18601-1", the second piece "-2", etc.
rsOut.AddNew ' add a new record to the table
rsOut!Serial = strPieces(0) ' output first record
rsOut.Update
strID = Left(strPieces(0), InStr(strPieces(0), "-") - 1) ' e.g. "18601"
For iPos = 1 To UBound(strPieces(0)) - 1
rsOut.AddNew
rsOut!Serial = strID & strPieces(iPos)
rsOut.Update
Next iPos
rsIn.MoveNext
Loop
End Sub

and this gets stuck on the UBound telling me it expects an array.

i have played with an array once before. i had to set the variable as a
variant. should that be the case here?

 

[John Spencer]

I think that should read

For iPos = 1 To UBound(strPieces) - 1


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[DawnTreader]

Hello John

here is the code currently:

Private Sub Command0_Click()
Dim strPieces() As String
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Dim iPos As Integer
Dim strID As String
Dim cDB As Database

Set cDB = CurrentDb
Set rsIn = cDB.OpenRecordset("utblSerialList", dbOpenDynaset,
dbOpenTable) '.OpenRecordset("utblSerialList", dbOpenDynaset)
Set rsOut = cDB.OpenRecordset("utblSerialList", dbOpenDynaset,
dbOpenTable) '.OpenRecordset("utblSerialList", dbOpenDynaset)

Do Until rsIn.EOF
If InStr(rsIn!serial, ",") <> 0 Then
Me.txtrsIn = rsIn!serial
strPieces = Split(rsIn!serial, ",") ' get an array of the pieces
' the first piece will be "18601-1", the second piece "-2", etc.
rsOut.AddNew ' add a new record to the table
rsOut!serial = strPieces(0) ' output first record
rsOut.Update
strID = Left(strPieces(0), InStr(strPieces(0), "-") - 1) ' e.g.
"18601"
For iPos = 1 To UBound(strPieces) - 1
rsOut.AddNew
rsOut!serial = strID & strPieces(iPos)
rsOut.Update
Me.txtrsOut = rsOut!serial
Next iPos
rsIn.MoveNext
End If
Loop
End Sub

this is stopping at the point of

Set rsOut = cDB.OpenRecordset("utblSerialList", dbOpenDynaset, dbOpenTable)
'.OpenRecordset("utblSerialList", dbOpenDynaset)

it gives me the error that the table is already opened exclusively by
another user.

and i think it is the line before it that is causing the problem because it
is the only other thing that has the table open when i hit my button.

any ideas?