The data you just posted had a different format. It omitted '<b>' before
'Task'. The data has to be consistent. Try this.
SELECT Bruce.ID, Mid([Comments],InStr([Comments],"Task:
<b>")+9,InStr([Comments],"</b>")-10) AS Task, IIf(InStr([Comments],"To:
<b>")=0,Null,Left(Mid([Comments],InStr([Comments],"To:
<b>")+7,InStrRev([Comments],"To:
<b>")),Len(Mid([Comments],InStr([Comments],"To:
<b>")+7,InStrRev([Comments],"To: <b>")))-4)) AS [User]
FROM Bruce;
--
KARL DEWEY
Build a little - Test a little
Bruce said:
Yes, thank you. Interesting results:
Actual Data
FileID Comments
346866 Task: <b>Examine</b><br>To: <b>William</b>
346866 Task: <b>Scanned Documents (Title)</b>
333100 Task: <b>Update Title/Prelim</b>
337759 Task: <b>Updated Title Product to Proposed Insured</b>
308141 Task: <b>Produce/Deliver Policy</b>
317133 Task: <b>Search</b><br>To: <b>Patrick</b>
Since there are different lenghts, I get different results (and no users):
FileID Task User
346866 amin
346866 anned Documents (Title
333100 date Title/Preli
337759 dated Title Product to Proposed Insure
333100 dated Title/Prelim Complet
308141 oduce/Deliver Polic
317133 arc
--
Thanks, Bruce
KARL DEWEY said:
Did you try it?
It works for me with the data you posted.
--
KARL DEWEY
Build a little - Test a little
:
Thanks, Karl. Here's the thing, in my attempt to be brief I may not have been
clear.
ID Comments (memo)
1 <b>Task: <b>SEARCH</b><br> To: <b>EARL</b>
2 <b>Task: <b>FIND</b>
3 <b>Task: <b>TRACH GOES OUT</b><br> To: <b>SHINY</b>
4 <b>Task: <b>SECOND COMING EXTENDED VER</b><br> To: <b>JC</b>
etc...
Many differnt lenghts, extra spaces, etc...
Thanks again
--
Thanks, Bruce
:
This will work but only if the string ends after the user.
SELECT Bruce.ID, Mid([Comments],InStr([Comments],"<b>Task:
<b>")+12,InStr([Comments],"</b>")-13) AS Task, IIf(InStr([Comments],"To:
<b>")=0,Null,Left(Mid([Comments],InStr([Comments],"To:
<b>")+7,InStrRev([Comments],"To:
<b>")),Len(Mid([Comments],InStr([Comments],"To:
<b>")+7,InStrRev([Comments],"To: <b>")))-4)) AS [User]
FROM Bruce;
--
KARL DEWEY
Build a little - Test a little
:
I've got a tables/records that looks like this:
ID Comments (memo)
1 <b>Task: <b>SEARCH</b><br> To: <b>EARL</b>
2 <b>Task: <b>FIND</b>
I'd like to parse them out into new fields that look like this:
ID Task User
1 SEARCH EARL
2 FIND (blank)
How do I delete the unwanted stuff and create the new strings? In one step?
I used a bunch of updates to delete the junk, and something like
Left([Comments],InStr([Comments]," ")-1) and
Mid([Comments],InStr([Comments]," ")-1)
afterwards, but where there is no second string, I get the first string
value:
ID Task User
1 SEARCH EARL
2 FIND FIND
How do I keep getting my Task in my user?
Thanks doesn't say enough, but
