Extracting Data from Beginning of Memo Field and Moving it to Other Fields

[LRSullivan]

I have a table that stores information about documents. Three of the fields in the table are Author, Recipient and Title fields. Some of the data in this table was imported from an Excel spreadsheet which didn't have Author or Recipient fields. Instead, the spreadsheet creator put the Author and Recipient data in the Title field in the following format: "AuthorName/RecipientName Title" (that's two spaces before the title, and it's the only time as far as I can tell that two spaces are used to separate words in the Title field). To complicate matters, there are a few cases where a / is used in the Title field but does not separate AuthorName and RecipientName. To further complicate matters, there are a few cases where RecipientName is a two word name (none that I saw, though, where AuthorName was a two word name). So here's the obvious question:



Is there some code I could use to automate the process of stripping the "AuthorName/RecipientName " off the front of the data in the Title field and updating the Author field with AuthorName and the Recipient name to the Recipient field with RecipientName?

[maximus]

LRSullivan welcome to the forum. Can u do something can u send me a sample file to work on as the problem is not very clear from your post. If you are not being able to upload the file due to the confidentiality reasons can u atleast give me a some arbitrary data

[LRSullivan]

I tried sending a message to Maximus with a bit of sample data embedded in the message, but my vBulletin skillz are not all that they should be. Instead, I'll try to attach the sample data file to this message, for Maximus and for anybody else interested in tackling this.

[djoosten]

Create the following sub-procedure and call it from a button on a form or from the Immediate Window:

Call ParseString

Here is the ParseString sub-procedure:


Public Sub ParseString()

Dim sInput As String
Dim rst As Recordset
Dim iPos As Integer 'To hold position of double space Title separator
Dim sAuthRecp As String 'Temporary holding spot for unparsed author and recipient

Set rst = CurrentDb.OpenRecordset("tblSample", dbOpenDynaset)
With rst
Do Until .EOF
.Edit
sInput = !Title
iPos = InStr(sInput, " ")
If iPos <> 0 Then 'There is a double-space meaning an author/recipient exists
!Title = Mid(sInput, iPos + 2)
sAuthRecp = Left(sInput, iPos - 1)
!Author = Left(sAuthRecp, InStr(sAuthRecp, "/") - 1)
!Recipient = Mid(sAuthRecp, InStr(sAuthRecp, "/") + 1)


End If
.Update


.MoveNext
Loop
.Close
End With
Set rst = Nothing

End Sub