Do you really want to insert each line into a separate column (field) in a
single row in a table? You could only really do this if there were a fixed
number of lines per memo, and each line represented a different attribute
type of whatever entity type the table represents. Moreover, the lines would
need to be in a consistent order. For example you could have a table
Employees, where line 1 of the memo is the employee's first name, line 2
their last name, line 3 their job title and so on.
If each line represents a distinct value of the same attribute type,
however, then you should insert each line into the same column in separate
rows of a table. The reference number would be repeated in each row per
current memo, and the table could then be related to another table on the
reference number field. Continuing the employees analogy, each line in the
memo field could represent a project assigned to the employee, in which case
you'd insert the values into a Projects table with the reference number
column as a foreign key referencing the primary key of an Employees table,
and a Project column containing each line of text from the memo field in a
separate row in the table. The approach Dave has suggested, iterating
through a recordset, would be the way to do this (though I think the line
'For lngI = 0 To Ubound(varMemoLines) – 1' should actually be 'For lngI = 0
To Ubound(varMemoLines)'. The Ubound function returns the higher limit of an
array's dimension, not the count of elements of the dimension, so there is no
need to subtract 1 as you would when using the Count property of a collection
for instance).
If the first scenario above is what you are looking for, inserting the lines
into separate columns in a row, then it can be done as a set operation using
an 'append' query. Firstly create a new table into which to insert the data.
With the example above lets say this has columns EmployeeID (the refernce
number), FirstName, Lastname and JobTitle. The current table would thus have
an EmployeeID column and a memo field with 3 lines per employee.
Use the Split function to extract the lines to an array as Dave describes.
Wrap this in a function like so:
Public Function SplitMemo(strMemo As String, intColumn As Integer) As String
Dim varMemoLines As Variant
varMemoLines = Split(strMemo, vbNewLine)
SplitMemo = varMemoLines(intColumn)
End Function
Then call the function in an 'append' query, passing a different value (from
0 upwards) for each of the columns into which a line from the memo field is
to be inserted:
INSERT INTO NewEmployeesTable (EmployeeID, FirstName, LastName, JobTitle)
SELECT EmployeeID,
SplitMemo([YourMemoField],0),
SplitMemo([YourMemoField],1),
SplitMemo([YourMemoField],2)
FROM CurrentEmployeesTable;
Ken Sheridan
Stafford, England
