Extract Parts of a Memo Field into Separate Fields

[MsTGordon]

I have a table where the data (memo field) contains various names seperated by the dreadful wildcard key (*). This data comes from another program using ODBC connection. Here is my issue.
I need to seperate each portion of it into a different text field. For example:

Engine Room/Starboard/Drawer-2 should be
Engine Room
Starboard
Drawer-2
In 3 seperate fields.

The data is not consistent. It may only have: Engine Room
or
Engine Room/ Starboard (yes the space may or may not come after the "/".

How can I fix this using a qry or two. I'm not good at VBA at all.

HELP!!!!

[TheSmileyCoder]

I have whipped up some example code your to look at. For this example I created a table tbl_Example with 4 fields, the first field I called tx_Example and entered your example data into, the next 3 fields simply named F1, F2, F3.

I then ran the following code on it:

Public Sub BytesExample()
    On Error GoTo err_Handler
    Dim rsD As DAO.Recordset
    Set rsD = CurrentDb.OpenRecordset("tbl_Example")
    
    
    
    Do While Not rsD.EOF
        rsD.Edit
        rsD!F1 = Trim(Split(rsD!Tx_Example, "/")(0))
        rsD!F2 = Trim(Split(rsD!Tx_Example, "/")(1))
        rsD!F3 = Trim(Split(rsD!Tx_Example, "/")(2))
        rsD.Update
        rsD.MoveNext
    Loop
    
sub_Exit:
    rsD.Close
    Set rsD = Nothing
    Exit Sub
    
err_Handler:
    If Err.Number = 9 Then
        Resume Next
    Else
        MsgBox "Err: " & Err.Number & vbNewLine & Err.Description
    End If

End Sub

The main things to note here, are the SPLIT function, which takes a string and splits it into an array based on the delimiter provided (in this case "/"). The trailing (0) and (1) are simply the positions in the resulting array. This is furthermore passed to the Trim function which will remove any trailing or leading spaces for you.

Since you mentioned that the input data might not always contain 3 elements, I added error code to handle that. In case only 2 elements are present in the input data, the third field assignment rsD!F3 = Trim(Split(rsD! Tx_Example, "/")(2))will cause an error, because there is no 3rd element in the array. In that case it will be catched by the error handler, and the program will resume with the next line.



I hope you will be able to take the example from here, and implement it yourself. You haven't really specified HOW you intend to use your information. Is this a one-time import of data, or do you want to constantly display data provided by an outside ODBC connection for example.

[MsTGordon]

I'm going to try this in a sample.
This is a new request from the user that will more than likely get repeated. I was able to give them a final product today using Excel (Data, Text To Column Feature) but would like to program something in Access to do it.
Thank you for responding.

[NeoPa]

That code illustrates the concept quite well. Another trick would be to replace lines #10 through #12 with these very similar lines :

        rsD!F1 = Trim(Split(rsD!Tx_Example & "//", "/")(0))
        rsD!F1 = Trim(Split(rsD!Tx_Example & "//", "/")(1))
        rsD!F1 = Trim(Split(rsD!Tx_Example & "//", "/")(2))