More query help needed!

  • Thread starter Thread starter Fred W
  • Start date Start date
F

Fred W

Hello fellow Access Users!

If some one would be able to assist me with this issue I
would appreciate it.

I have a field that may contain anywhere between 0 and 8
values separated by commas. I need a query that separate
each of these values in to its own field. Below is an
example of what I typically see for values in this field.

Subject
1000,1004
10,1000,1004
9,1,1004
10,9,1004

Anyone have any thoughts?

Fred
 
I doubt you can do this with a query - I think you'll need VBA code to do
it.

I assume that you're trying to sort out a poorly designed table. If so, take
the opportunity to design the tables properly, which probably means a new
table with one value in each field, rather than 8 new fields.

The following code may help - it will split up your field and write each
value into the intermediate window. It should not be too much more work to
get it to write the values into a table:

Public Function ParseData() As Boolean

Dim strWords() As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("NameOfTable")

With rst
Do Until .EOF
strWords = Split(!Subject, ",")
Debug.Print !ID,
For i = LBound(strWords) To UBound(strWords)
Debug.Print strWords(i),
Next
Debug.Print ""
.MoveNext
Loop
End With

ParseData = True

End Function
 
Back
Top