Delete some data from a memo field

[tbjohnson2]

I have a memo field that holds some resume information in
a table. The contents of the field are the resume data
and then after that there is a line in the field that
states <<<ENDText>>> and then additional information. I
would like to remove all the text below the <<<ENDText>>>
line. I have about 20,000 records to do this on.

Any suggestions?

Thanks!

 

[Naresh Nichani MVP]

Hi:

Could you try this --

'Assume tbl1 is table name and Mem is field name to search

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strVal As String
Dim lngPos As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [tbl1]", dbOpenDynaset)

Do While Not rs.EOF
If Not IsNull(rs.Fields("Mem")) Then
strVal = rs.Fields("Mem") ' this is memo field
lngPos = InStr(1, strVal, "<<END TEXT>>", vbTextCompare)
If lngPos <> 0 Then
strVal = Mid(strVal, 1, lngPos - 1)
rs.Edit
rs.Fields("Mem") = strVal
rs.Update
End If
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Naresh Nichani
Microsoft Access MVP

 

[TC]

Or, er:

UPDATE [tbl1] SET [Mem] = Left$ ([Mem], Instr ([Mem] & "<<END TEXT>>",
""<<END TEXT>>") - 1)

TC

Hi:

Could you try this --

'Assume tbl1 is table name and Mem is field name to search

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strVal As String
Dim lngPos As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [tbl1]", dbOpenDynaset)

Do While Not rs.EOF
If Not IsNull(rs.Fields("Mem")) Then
strVal = rs.Fields("Mem") ' this is memo field
lngPos = InStr(1, strVal, "<<END TEXT>>", vbTextCompare)
If lngPos <> 0 Then
strVal = Mid(strVal, 1, lngPos - 1)
rs.Edit
rs.Fields("Mem") = strVal
rs.Update
End If
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

Naresh Nichani
Microsoft Access MVP

I have a memo field that holds some resume information in
a table. The contents of the field are the resume data
and then after that there is a line in the field that
states <<<ENDText>>> and then additional information. I
would like to remove all the text below the <<<ENDText>>>
line. I have about 20,000 records to do this on.

Any suggestions?

Thanks!