Extracting text from a field

[bwash70]

Hey folks.



I have an MS Excel extract from a java application that we use for our reporting purposes.

What I need to do is extract the 'Resolution' details out of the extracted excel field 'Work log'.

To do this I need to find the 'resolution time' in the 'resolution field', delete all text before the 'resolution time' in the 'Work Log' field, then find the next instance of a time stamp and delete that time stamp and all text after it.

All attempts at this have been unsuccessful this far.

An example of the 'Work Log' field would be:
"03/01/2010 10:30:01 Case has been closed 03/01/2010 10:15:22 User restarted computer and all is working ok 03/01/2010 10:10:11 User called to advise that they are unable to access their network drives"

An example of the 'Resolution Time' field would be:
"03/01/2010 10:15:22"

So basically, I am looking to remove anything in the worklog before the resolution time, then everything from the next date & time stamp onwards.

In the example above I want to be left with:
"User restarted computer and all is working ok"

Any help would be much appreciated
Cheers
Bwash70

[bwash70]

I should note. The extract is in Excel, but I would like to do this through access / vba / sql if possible.

[ssanfu]

Here are two examples of parsing the text string you provided....

'-------------------------------------------------
'   Doesn't use the 'Resolution Time' field
'-------------------------------------------------
Public Sub ParseResolution()
   Dim String2Search As String
   Dim pos As Integer

   pos = 1

   'String2Search = "03/01/2010 10:30:01 Case has been closed 03/01/2010 10:15:22 User restarted computer and all is working ok 03/01/2010 10:10:11 User called to advise that they are unable to access their network drives"
   String2Search = "03/01/2010 10:30:01 Case has been closed 03/01/2010 10:15:22 User restarted computer and all is working ok 03/01/2010 10:10:11 User called to advise that they are unable to access their network drives"

   'find first colon
   pos = InStr(pos, String2Search, ":") + 1
   'find second colon
   pos = InStr(pos, String2Search, ":") + 1

   'find third colon
   pos = InStr(pos, String2Search, ":") + 1

   'find fourth colon
   pos = InStr(pos, String2Search, ":") + 2

   'cut off first part of string
   String2Search = Mid(String2Search, pos)

   'now locate the end of the string
   pos = 1
   pos = InStr(pos, String2Search, "/")
   pos = pos - 3

   'cut off back part of string
   String2Search = Trim(Left(String2Search, pos))

   MsgBox String2Search
End Sub

'-------------------------------------------------
'   Uses the 'Resolution Time' field
'-------------------------------------------------
Public Sub ParseResolution2()
   Dim String2Search As String
   Dim SearchFor As String
   Dim pos As Integer

   pos = 1

   'String2Search = "03/01/2010 10:30:01 Case has been closed 03/01/2010 10:15:22 User restarted computer and all is working ok 03/01/2010 10:10:11 User called to advise that they are unable to access their network drives"
   String2Search = "03/01/2010 10:30:01 Case has been closed 03/01/2010 10:15:22 User restarted computer and all is working ok 03/01/2010 10:10:11 User called to advise that they are unable to access their network drives"

   ' Get date/time from 'Resolution Time' field
   'SearchFor =  address for 'Resolution Time'  in excel

   SearchFor = "03/01/2010 10:15:22"   ' <<<< hard code for now

   'locate the start of string to keep
   pos = InStr(pos, String2Search, SearchFor) + 20

   'cut off first part of string
   String2Search = Mid(String2Search, pos)

   'now locate the end of the string
   pos = 1
   pos = InStr(pos, String2Search, "/")
   pos = pos - 3

   'cut off back part of string
   String2Search = Trim(Left(String2Search, pos))

   MsgBox String2Search
End Sub

Note that there is not any Error Handling code in the examples
.

[bwash70]

Hey Steve - Thank you for that. Both worked really well when I tested them.

Unfortunately now I have found another issue where there can be two or three time stamps equal to the resolution time field so I need a new approach.

These extra entries are a bit more uniform so I should be able to remove that information before I move onto extracting the resolution.

I'll be back in touch!
Cheers

[pkstormy]

Not sure if this helps but I created this:
https://www.accessforums.net/code-re...ords-7690.html

which is setup so that you can enter certain syntax (such as : ) and define the instance of that syntax and a value to grab after it finds that syntax. It will parse a string such as you have and filter out the values after the syntax (saving that value to a table). It stops grabbing the value after it finds the syntax when it sees a space which probably won't work for your situation though.

The search strings mine had to search through looked like this:
The message FAILED with a RC:1 AT: 23:44 in from S:TCDNM01, on ....

and I wanted to grab the values after RC: (1) and after AT: (23:44) and after S: (TCDNM01 but excluding the 01).

The table I use to configure what syntax to search for has these fields (the table is called: tblSearchItems):
MySearchValue (syntax value you want to find)
ExcludeCharInValue (characters to exclude in syntax above)
StoreValueInField (Field name to store the value after the syntax above) (in the tblStoreValues table)

and I would enter criteria syntax records such as RC: and AT: and S: in the above 'MySearchValue' field. (I could've also just used : - a colon.) For the S: criteria search record, I used 01 to 'ExcludeCharInValue'. And for 'StoreValueInField' would be the fieldname I wanted to store the value in (in the tblStoreValues table). - See mdb in the attachment in the post.

It's setup so that I could easily add/delete search criteria records in the tblSearchItems table (because the search string changed periodically) and I could tell it where to store certain values within the string without needing to change any coding.

You can enter an unlimited types of criteria syntax records in the tblSearchItems table to search for, telling it what field names (in another table) to store those values in after it finds the syntax. For example, if you entered a criteria record to search for : and another criteria record : to search for again, for the 2nd colon it will grab the value after it and store that value in whatever field you tell it to save that value in (which can be a different field to save the value to versus the 1st colon).

In the tblSearchItems, originally I had set it up with a field called: MyValueLength to only grab X characters after it found the syntax but I then changed it to grab all values after the syntax until it hit a space because sometimes the values would change in length. ie. TCDNM01 could end up being TCDNMXA01 for one search string record and then TCDNMXADDFD for the next search string record. I'm also pretty sure I set it up so that it skips any spaces before it finds the value. (ie. S:<space>TCDNMXADDFD or S:<space><space><space>TCDNMXADDFD = TCDNMXADDFD) The code though also stops grabbing the value when it sees the first space after the value which wouldn't work in your situation.

In the mdb, there's a form which allows you to paste a search string and run the search (or you could run the search code against a table with multiple search string records - see code in the OnClick event of the cmdSearch button on the SearchForm.) The tables are currently populated with test values which you can delete. tblSearchStrings is the table I would import the excel search string data to.

If you wanted to try the example mdb, you'd want to enter 4 colon (records) in the MySearchValue field in tblSearchItems. Leave the ExcludeCharInValue and StoreValueInField blank for the first 3 colons. On the 4th colon record, enter 0123456789 for ExcludeCharInValue. And also for the 4th colon record, enter a name in StoreValueInField in which to save the value to (and make sure this name also matches a fieldname in the tblStoreValues table.) It would then parse out a sentence such as "User restarted computer and all is working great", saving this to the field you told it to save it to. You'll probably need to find a way though in the code to tell it when to stop grabbing the value since I'm pretty sure it's setup to stop when it sees the first space after the value (ie. it will probably just save "User" and not the rest).

I haven't tested the above for your situation and keep in mind that I created this code years ago but I believe the above would work to do some of what you want to. Although ssanfu's example is probably better than this example. I hope some part of the setup/code helps though. It's a little different approach.