Pulling specific data from a single field

[Term Limits Now]

I am an intermediate level Access 2002 user.

I have inherited a table with a field that contains multiple entries.
Possible field formats are;
",123,"
",123,123,"
",123,123,123,"
and
[ no value in field ]

Each three number series represents a specific code. Locations of
commas, as separators and as the starting and ending character, are as
noted.

I would like to identify and move the individual codes into a
distinct field (column).

Any ideas would be appreciated.

Thanks

 

[Jeff Boyce]

If you were to ask a human to pull apart the data in this field, would they
understand where they were to put the pieces, under what conditions? You'll
need to be even more explicit for Access to do it.

Take a look at Access HELP on the following functions: Left(), Mid(),
Right(), Len(), Instr()

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

Air code:

Dim rst As DAO.Recordset
Dim strSql As String

strSql = "select * from mytable where DataField is not null"

Set rst = CurrentDb.OpenRecordset(strSql)
Do While rst.EOF
rst.Edit
rst!Field1 = Split(rst!DataField, ",")(0)
rst!field2 = Split(rst!DataField, ",")(1)
rst!field3 = Split(rst!DataField, ",")(2)
rst.Update
rst.MoveNext
Loop
rst.Close


note that above code assumes that the target field allows a zero lenght
field.

if you like me, you likey don't allow those zl fields.

So, we could use:

Dim rst As DAO.Recordset
Dim strSql As String
vBuf As Variant

strSql = "select * from mytable where DataField is not null"

Set rst = CurrentDb.OpenRecordset(strSql)
Do While rst.EOF
rst.Edit
vBuf = Split(rst!DataField, ",")
If Len(vBuf(0)) > 0 Then rst!Field1 = vBuf(0)
If Len(vBuf(1)) > 0 Then rst!Field2 = vBuf(1)
If Len(vBuf(2)) > 0 Then rst!Field3 = vBuf(2)
rst.Update
rst.MoveNext
Loop
rst.Close

 

[Douglas J. Steele]

I don't think so, Albert.

For ",123,", Split will give you a 3 element array, and you'd refer to
Split(rst!DataField, ",")(1) to get the 123.

Similarly, for ",123,123,", you'd refer to Split(rst!DataField, ",")(1) to
get the first 123, and Split(rst!DataField, ",")(2) to get the second one.

Finally, for ",123,123,123,", you'd refer to Split(rst!DataField, ",")(1),
Split(rst!DataField, ",")(2) and Split(rst!DataField, ",")(3)

I think what's required is:

Dim rst As DAO.Recordset
Dim strSql As String
Dim vBuf As Variant

strSql = "select * from mytable where DataField is not null"

Set rst = CurrentDb.OpenRecordset(strSql)
Do While rst.EOF
rst.Edit
vBuf = Split(rst!DataField, ",")
If UBound(vBuf) > 1 Then rst!Field1 = vBuf(1)
If UBound(vBuf) > 2 Then rst!Field2 = vBuf(2)
If UBound(vBuf) > 3 Then rst!Field3 = vBuf(3)
rst.Update
rst.MoveNext
Loop
rst.Close

 

[Albert D. Kallal]

ah, excellent....

I had not noticed that the data *always* had a "leading" comma...

 

[Pieter Wijnen]

not to be a nitpicker (but as always I am)
under what conditions will
Do While rst.EOF ever do anything?

I'd personally use:

While not rst.EOF
...
Wend

Pieter

 

[Albert D. Kallal]

"Pieter Wijnen"

not to be a nitpicker (but as always I am)
under what conditions will
Do While rst.EOF ever do anything?

I'd personally use:

While not rst.EOF
...
Wend

Pieter

Ah...nit pick is good here!!!

Always open to corrections!!!

And....yes, I normally use

do while rst.eof = false