Split Memo Field in a query

[Kodash]

Hi Guys,
Im not sure if this should be here or in vba?
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.

My current query looks something like:
ID | Product | Pack Size
1 | item a | 1x1000,1x1050
2 | item b | 1x1000,20x25
3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize
1 | Item a | 1x1000
1 | Item a | 1x1050
2 | Item b | 1x1000
2 | Item b | 20x25
etc.

I think im supposed to use the Split() Function though i dont have a clue wherw to start.
Thanks in Advance

 

[boerbende]

Is it a possibility to use different columns?
and then something like

Startpos=Instr (1,Yourstring, "|")
column 1: Left(string,startpos)
column 2: Mid(string,startpos,Instr (startpos+1,Yourstring, "|"))
column 3: Right(string,strlen(string)-startpos - Instr (startpos+1,Yourstring, "|"))

(not verified, but just a wild idea)

 

[plog]

Yeah this is going to take VBA. I'd search the forum, because this is a common question, maybe there is code you can steal.

Essentially, you create a table to hold the proplerly structured data, then run VBA to loop through the existing table and insert the correct data into the proper table. It's going to involve recordsets, looping and executing SQL statements.

 

[jdraw]

Did you create the memo field, or did you get handed this database and table?

You may be able to
Dim arrSplit() As String
then use
arrSplit = Split(strName, "|")

This would put each string between | into an array entry.
You could use split again to separate the substrings that are separated by ",".
And then move the fragments to the proper record(s).

 

[Kodash]

It was handed to me. The data in the table is used in a report elsewhere so I can't really change the layout.

Im looking into Split() at the moment, am I on the right track?

 

[Kodash]

boerbende, I don't think Columns will work I'm afraid. The plan is to use each row as a set of variables in another application (NiceLabel if it's of any consolation)

 

[jdraw]

I think so. Can you post a sample of the data or a copy of your database?
All we need is the table structure and a few records --not everything and NOTHING confidential. Is the , really a comma, or CRLF?

So you have a table something like the attached jpg?

PS: I misunderstood the | in your original. I thought you had a memo field with all the other fields/records separated by |.

 

[Kodash]

Please Find attached. Also to give credit where it's due, the whole database was developed by a third party (I dont know if by sharing this I've just plagiarized the guy )

 

[Kodash]

Jdraw that's essentially exactly what i was on about lol

 

[jdraw]

Here's some code (based on my sample). I'll look at your data.

Sub kodash1()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim arr() As String
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblKodash")
    Do While Not rs.EOF
        arr = Split(rs!packsize, vbCrLf)
        For i = LBound(arr) To UBound(arr)
            Debug.Print rs!MyPK & "  " & arr(i)
        Next i
        rs.MoveNext
    Loop
End Sub

Here's the output from my test data

1   1x1000
1  1x1050
1  2x4500
1  2x750
1  3x2001
2  2x2450
2  3x221
2  1x2345

 

[jdraw]

Are you ok with the code as a guide?

You can process your Additional Info and create a new table, with 1 packsize per record and enough info to match it to your other table.

 

[Kodash]

Yes thank you very much. I should be able to take it from here