Trunc Memo record

[Brad]

I need to take a Memo field and break the text into seperate records
38 characters long so long as there is still text and append each of
the seperate records into another table. I can do this with many
multiple append queries but I know there is a way to do this with VBA
that would be a lot easier. Anybody got any sample code.

 

[Brad]

I need to take a Memo field and break the text into seperate records
38 characters long so long as there is still text and append each of
the seperate records into another table.  I can do this with many
multiple append queries but I know there is a way to do this with VBA
that would be a lot easier.  Anybody got any sample code.

Here is the code I have tried and get an Object Required Error, don't
know why and if I get that fixed I don't know if this will work. Any
help will be appreciated.

Sub TruncNote()
Dim counter As Integer
Dim lineNo As Integer

counter = 1
lineNo = 1
Do
DoCmd.RunSQL "INSERT INTO Comments([NDate], [Note], [Line])
Values(#" & Query![comconv].[NDate] & "# , " & Mid(Query![comconv].
[Note], counter, counter + 38) & " , " & lineNo & ")"

counter = counter + 38
lineNo = lineNo + 1
Loop Until IsNull(Mid(Query![comconv].[Note], counter, counter +
38))

'


End Sub

 

[Paolo]

Hi Brad,
I called table1 the table containing the memo fields and fld1 the memo
field, table2 the table where I append the 38char chuncks and fld_trgt the
field in table2 where I put the chuncks of the memo. Substitute in the
following code the names of your objects and the trick is done

Set rec = CurrentDb.OpenRecordset("select * from table1", dbOpenDynaset)
Set trgt = CurrentDb.OpenRecordset("select * from table2", dbOpenDynaset)
Do While Not rec.EOF
num_seg = Len(rec![fld1]) \ 38
For i = 1 To num_seg
trgt.AddNew
trgt!fld_trgt = Mid(rec!fld1, (38 * (i - 1) + 1), 38)
trgt.Update
Next i
If Len(rec![fld1]) Mod 38 <> 0 Then
trgt.AddNew
trgt!fld_trgt = Mid(rec!fld1, (38 * (i - 1) + 1), 38)
trgt.Update
End If
rec.MoveNext
Loop
MsgBox "done"

HTH Paolo

I need to take a Memo field and break the text into seperate records
38 characters long so long as there is still text and append each of
the seperate records into another table. I can do this with many
multiple append queries but I know there is a way to do this with VBA
that would be a lot easier. Anybody got any sample code.

Here is the code I have tried and get an Object Required Error, don't
know why and if I get that fixed I don't know if this will work. Any
help will be appreciated.

Sub TruncNote()
Dim counter As Integer
Dim lineNo As Integer

counter = 1
lineNo = 1
Do
DoCmd.RunSQL "INSERT INTO Comments([NDate], [Note], [Line])
Values(#" & Query![comconv].[NDate] & "# , " & Mid(Query![comconv].
[Note], counter, counter + 38) & " , " & lineNo & ")"

counter = counter + 38
lineNo = lineNo + 1
Loop Until IsNull(Mid(Query![comconv].[Note], counter, counter +
38))

'


End Sub