Moving data to the left of a double-space from one field to another

[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? I was thinking I could use an intermediate step to strip everything to the left of the double-space and move it to the Recipient field, and then strip everything to the left of the / from the Recipient field and move it to the Author field. Then, of course, I'd have to clean up the / and the double-space. I've been looking at the Left and Left$ functions but I just don't feel like I've quite got a grip on how to use them this way. Any thoughts?

I've attached a small database file with two tables in it with sample records. The first is how stuff looks now, the second is how I'd like it to look. I'd really appreciate any help getting from here to there!

 

[DCrake]

One problem you have is that the second element may have more than one word in it as per "Dist List" It would be hard to tell when the element finishes and the new one starts.

 

[Brianwarnock]

You can select the third element with

IIf(InStr([title],"  ")=0,[title],Right([title],Len([title])-InStrRev([title],"  ")-1))

there are 2 spaces between the " "


I'm sure that you can figure out the rest

Brian

 

[LRSullivan]

Thanks for your replies. !

DCrake, I was hoping that since the second element is separated from the third by two spaces rather than by a single one there might be a way to convince Access to recognize the distinction between the second element and the third even though sometimes the second element does contain a single space.

Brian, if I read it right, the code you've provided will let me remove all the stuff to the left of the title from the Title field, but won't preserve that stuff so that I can move it into the Author and Recipient fields. But maybe I'm missing something obvious. In any case, I'll play around with what you've given me and see how it goes -- thanks!!

 

[Brianwarnock]

I wasn't removing anything, merely selecting, the way i see it you are best using a make table query with this field split.
to create the 3 fields

Author: IIf(InStrRev([title],"  ")>0,Left([title],InStr([title],"/")-1),"")

Recipient: IIf(InStrRev([title],"  ")=0,"",Mid([title],InStrRev([title],"/",InStrRev([title],"  "))+1,InStrRev([title],"  ")-InStrRev([title],"/",InStrRev([title],"  "))-1))

That was fun, overlooked the date in the Title at first

newtitle: IIf(InStr([title],"  ")=0,[title],Right([title],Len([title])-InStrRev([title],"  ")-1))

Brian


PS don't forget that the Instr and the InstrRev are checking for 2 spaces not one.

 

[LRSullivan]

Brian, thanks again!! I've got it working beautifully now -- very exciting!

 

[lutajuca]

Maybe something like this :

Query for update :
UPDATE copytblsample AS a SET Author = fAuthor(a.title), Recipient = fRecipient(a.title), Title = fTitle(a.title);

Public functions in Modules :

Public Function fAuthor(xTitle) As String
If InStr(1, xTitle, " ") = 0 Then
fAuthor = "?" ' ?
Else
fAuthor = Left(xTitle, InStr(1, xTitle, "/") - 1)
End If
End Function

Public Function fRecipient(xTitle) As String

Dim InB As Integer, Inc As Integer
InB = InStr(1, xTitle, " ")
If InB = 0 Then
fRecipient = "?" ' ?
Else
Inc = InStr(1, Left(xTitle, InB - 1), "/")
fRecipient = Right(Left(xTitle, InB - 1), Len(Left(xTitle, InB - 1)) - Inc)
End If

End Function

Public Function fTitle(xTitle) As String
Dim InB As Integer
InB = InStr(1, xTitle, " ")
If InB = 0 Then
fTitle = xTitle ' ?
Else
fTitle = Right(xTitle, Len(xTitle) - InB - 1)
End If

End Function

Note :
copytblsample – table with only those records or put where … in query



late, sorry ...

 

[vbaInet]

Instead of "" I would use Null. Example:

This -->

Author: IIf(InStrRev([title],"  ")>0,Left([title],InStr([title],"/")-1),[COLOR=Red]""[/COLOR])

becomes this -->

Author: IIf(InStrRev([title],"  ")>0,Left([title],InStr([title],"/")-1), [COLOR=Red]Null[/COLOR])

 

[DCrake]

My issue is that there can be so many variations of the incoming data that one simple test will not suffice.

Firstly, is there a slash in the code?
if so, where is it?
Does it come before or after a word?
Is there anything after the slash?
Is there infact a space in the string? if not is it an Author? Title, Description?
Are there any double spaces? If so, does it appear before or after a slash?, if there is a slash in the string iin the first place.
What happens if there are 4 words and three single spaces? no slashes, no double spaces. How do we interpret the string?

What happens if there are co authors and they are split by a slash?
I could go on and on.....
The decision tree could go on for ever

 

[Brianwarnock]

vbaInet

Why Null not "" I had no reason but you do, it would be helpful for others to know why.

DCrake

The poster laid down some rules, they may be incomplete or broken, so do we do nothing? Surely it is better to go with what we know and visualy check for others.

Brian

 

[vbaInet]

Hi Brian,

vbaInet

Why Null not "" I had no reason but you do, it would be helpful for others to know why.

With Null it indicates that that field has no value and hence nothing is saved. If Allow Zero Length property is set to No then the code will error. Also, it will introduce the "problem" of testing for Null and "".

 

[DCrake]

Brian, my problem is that I am a pessamistic programmer who always thinks adead and expects the unexpected to happen in an attempt to cover all eventualities and where possible include as much future proofing as possible.

The more I put in a the beginning means the less I have to revise at a later stage when other outside influences can and does affect the original logic and methodology.

 

[Brianwarnock]

Hi Brian,
With Null it indicates that that field has no value and hence nothing is saved. If Allow Zero Length property is set to No then the code will error. Also, it will introduce the "problem" of testing for Null and "".

Funnily enough on another thread where I had done a similar thing I had used Null, and yesterday I had started out thinking Null, don't know why I used "" and then couldn't think of a reason to change and was too tired to chase it , so thanks.

Brian

 

[Brianwarnock]

Brian, my problem is that I am a pessamistic programmer who always thinks adead and expects the unexpected to happen in an attempt to cover all eventualities and where possible include as much future proofing as possible.

The more I put in a the beginning means the less I have to revise at a later stage when other outside influences can and does affect the original logic and methodology.

All good experienced programmers have the same philosophy, but eventually we must draw a line in the sand and do something, often a first pass expecting errors can be a useful tool to highlight things not thought about.

Brian