Parsing Out Text String(s) Into New Fields

[Anita Taylor]

We have a work order tracking database (purchased software – I did not
design). I need to find out how many work orders are being assigned to an
individual on a daily basis versus how many they are actually closing. There
is no such report function in the program – there are reports, but none that
show this particular information.

The database is structured so that the ONLY way I know a ticket is assigned
from one person to another is a text field that contains the following:

“cs - Assigned Issue To: FRED FLINSTONE Assigned by: BARNEY RUBBLEâ€

I need to get the Assigned To and Assigned By into two separate fields so I
can compare and report on them. Any ideas/suggestions would be greatly
appreciated.

1) the name of the Assigned To and Assigned By is always in all caps
2) there is no set amount of spaces between Assigned To and the analyst name
and Assigned by and the analyst name – the names grow to be as long as it
needs to be.

 

[krissco]

We have a work order tracking database (purchased software - I did not
design). I need to find out how many work orders are being assigned to an
individual on a daily basis versus how many they are actually closing. There
is no such report function in the program - there are reports, but none that
show this particular information.

The database is structured so that the ONLY way I know a ticket is assigned
from one person to another is a text field that contains the following:

"cs - Assigned Issue To: FRED FLINSTONE Assigned by: BARNEY RUBBLE"

I need to get the Assigned To and Assigned By into two separate fields so I
can compare and report on them. Any ideas/suggestions would be greatly
appreciated.

1) the name of the Assigned To and Assigned By is always in all caps
2) there is no set amount of spaces between Assigned To and the analyst name
and Assigned by and the analyst name - the names grow to be as long as it
needs to be.

Here is a working example.

Private Sub ATest()

'This character will replace "Assigned by:" in the string.
'It is used as a delimeter when breaking the string into tokens.
Const characterThatNeverAppearsInString = "|"

'Variables - You won't need strTxt.
Dim intToPosition As Integer
Dim strTo As String, strBy As String, strTxt As String
Dim arrNames() As String

'You won't need to assign strTxt
strTxt = "cs - Assigned Issue To: FRED FLINSTONE Assigned by: BARNEY
RUBBLE"
strTo = "Assigned Issue To:"
strBy = "Assigned By:"

'Find out where the assigned to name begins.
intToPosition = InStr(1, strTxt, strTo)
intToPosition = Len(strTo) + intToPosition

'Remove all information prior to the first name.
strTxt = Mid(strTxt, intToPosition)

'Replace the "Assigned By:" with our special character
strTxt = Replace(strTxt, strBy, characterThatNeverAppearsInString)

'Split on the character returning an array with two values
arrNames = Split(strTxt, characterThatNeverAppearsInString)

'Print "FRED FLINSTONE"
Debug.Print trim(arrNames(0))

'Print "BARNEY RUBBLE"
Debug.Print trim(arrNames(1))
End Sub



You could modify this into a public function, and call it with the
string you need to parse, and which token you want (first name or
second name) as parameters - then return the appropriate name.

-Kris

 

[fredg]

We have a work order tracking database (purchased software ¡V I did not
design). I need to find out how many work orders are being assigned to an
individual on a daily basis versus how many they are actually closing. There
is no such report function in the program ¡V there are reports, but none that
show this particular information.

The database is structured so that the ONLY way I know a ticket is assigned
from one person to another is a text field that contains the following:

¡§cs - Assigned Issue To: FRED FLINSTONE Assigned by: BARNEY RUBBLE¡¨

I need to get the Assigned To and Assigned By into two separate fields so I
can compare and report on them. Any ideas/suggestions would be greatly
appreciated.

1) the name of the Assigned To and Assigned By is always in all caps
2) there is no set amount of spaces between Assigned To and the analyst name
and Assigned by and the analyst name ¡V the names grow to be as long as it
needs to be.

Create a query that will be used as the Report's record source (or add
these new columns to an existing query).

Add 2 new columns to the query grid.

To: Mid([CombinedNames],InStr([CombinedNames],"Assigned Issue
To:")+19,(InStrRev([CombinedNames],"Assigned
by:")-1)-(InStr([CombinedNames],"Assigned Issue To:")+19))

By: Mid([CombinedNames],InStrRev([CombinedNames],"Assigned by:")+13)

Then simply include the [To] and [By] fields in the report.

 

[Anita Taylor]

Thanks for the reply!

The names are constantly changing - I just used Fred and Barney as examples.

Will your example work regardless of how long the names in each case are?
Your solution seemed to rely on there alwasy being the same number of
characters between Assigned By and Assigned To, and that, unfortunately, will
not be the case.

 

[Anita Taylor]

I'm not exactly where to put the code you're recommending. Should it be put
in the On Open action of the report? Will this work regardless of the names
being used - there could be dozens of different name combinations.

 

[fredg]

Thanks for the reply!

The names are constantly changing - I just used Fred and Barney as examples.

Will your example work regardless of how long the names in each case are?

1) Isn't this most easily established by the simple expedient of YOU
trying it?

Your solution seemed to rely on there alwasy being the same number of
characters between Assigned By and Assigned To, and that, unfortunately, will
not be the case.

2) Upon what do you base this statement?
The number 19, for instance, refers to the number of characters in the
phrase "Assigned Issue To:" plus 1, not to the number of characters in
the name "Fred Flintstone" which is only 15 characters.
See #1 above.

3) It's a courtesy to all readers of this (or any) news group to
include the relevant portion of any previous messages.
Someone who reads this message will have no idea of what you are
referring to, nor did I, without having to dig out the previous
message. Note that my previous reply to you included your original
question. Note also that this message includes your message.

 

[krissco]

Thanks for the reply!

The names are constantly changing - I just used Fred and Barney as examples.

Will your example work regardless of how long the names in each case are?
Your solution seemed to rely on there alwasy being the same number of
characters between Assigned By and Assigned To, and that, unfortunately, will
not be the case.

Short answer: Yes (always work). No (relied on fixed lengths).

The only hard-coded data in the Sub I posted is:
strTo = "Assigned Issue To:"
strBy = "Assigned By:"
(I'm assuming you will modify the function to pass in the string you
need parsed).

All is well as long as these two strings appear verbatim within your
string to parse.

The 19 in fredg's post refers to the length of "Assigned Issue To: "
Type ?len("Assigned Issue To: ") in the debug window (CTRL+G).

-Kris

 

[Anita Taylor]

Thanks so much for the reply. After trying it, see how it's working. I
apologize for not trying it first and for not following generally accepted
reply protocol.

I've been working on-call for the last two weeks and am severely sleep
deprived. A close friend's mother is undergoing quadruple bypass right now
and the prognosis is not very good. This weekend, my home was vandalized and
I've been working with the police and insurance investigators to file claims
and have the damage repaired.

It has been a very difficult couple of days and my mind is muddled. I was
asked for this information in a "gotta have it right away" mode.

I am again very thankful for all the responses and suggestions. I'm well on
my way now.