Extracting Partial Text from Field - INSTR and INSTRREV

[Anita Taylor]

A couple of weeks ago, I posted to this group for help extracting the To and
By data from this field into two separate fields I could query and report on

cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY

I was given this and it worked GREAT:

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

I now have an Assigned To field that contains MARILYN MONROE and an Assigned
By that contains ROBERT KENNEDY.

The problem is that I don't understand how the function works and am trying
to modify it to do basically the same thing on another field, as shown below:

cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned by:
ELROY JETSON

I need essentially the same thing - the name after TO and the name after
Re-assigned by.

So far, I've worked out this:

To: Mid([action_description],InStr([action_description],"To:")+4)

It gives me

JUDY JETSON Re-assigned by: ELROY JETSON

I need to figure out how to lop off everything from Re-assigned by to the
end. I basically have to figure out where Re-assigned by starts (character
position), then subtract everything that comes after that character position
- but I'm just not getting it and could use a little more help.

Having the complete, working statement would be great. Having someone
explain the original solution to me in "layman's terms" would be even better
and make it possible for me to use this solution in the future.

This group is always very helpful and I appreciate any advice or explanation
you can provide.

 

[Ken Sheridan]

Rather than messing around with convoluted expressions for this I'd be
inclined to write a few functions to do it. That way they are reusable
anywhere in the database. So, from the Modules tab of the database window
select 'New' and when the new module opens paste in the following code in
place of the few lines already there. You'll then find it creates three
separate functions in the module, which you can select individually from the
combo box at the top right of the module window:

''''code starts''''
Option Compare Database
Option Explicit


Public Function ReassignFrom(strAssignments As String) As String

Const conASSIGNTYPE = "Re-assign Issue From:"
Const conNEXTASSIGNTYPE = "To:"
Dim intStart As Integer, intLength As Integer

' get position where name starts by using Instr function
' to get start of assignment type string and then adding length
' of assignment type string to it
intStart = InStr(strAssignments, conASSIGNTYPE) + Len(conASSIGNTYPE)

' get position where name ends by using Instr function
' to find next assignemnet type string, and subtract start
' position of name to give length of name
intLength = InStr(strAssignments, conNEXTASSIGNTYPE) - intStart

' get name by means of Mid function, trimming off
' leading space, and set as function's return value
ReassignFrom = Trim(Mid(strAssignments, intStart, intLength))

End Function

Public Function ReassignTo(strAssignments As String) As String

Const conASSIGNTYPE = "To:"
Const conNEXTASSIGNTYPE = "Re-assigned by:"
Dim intStart As Integer, intLength As Integer

' get position where name starts by using Instr function
' to get start of assignment type string and then adding length
' of assignment type string to it
intStart = InStr(strAssignments, conASSIGNTYPE) + Len(conASSIGNTYPE)

' get position where name ends by using Instr function
' to find next assignemnet type string, and subtract start
' position of name to give length of name
intLength = InStr(strAssignments, conNEXTASSIGNTYPE) - intStart

' get name by means of Mid function, trimming off
' leading space, and set as function's return value
ReassignTo = Trim(Mid(strAssignments, intStart, intLength))

End Function

Public Function ReassignBy(strAssignments As String) As String

Const conASSIGNTYPE = "Re-assigned by:"
Dim intStart As Integer

' get position where name starts by using Instr function
' to get start of assignment type string and then adding length
' of assignment type string to it
intStart = InStr(strAssignments, conASSIGNTYPE) + Len(conASSIGNTYPE)

' get name by means of Mid function, trimming off
' leading space. Note that if no length argument
' is passed into Mid function it will return substring
' up to the end of the string
ReassignBy = Trim(Mid(strAssignments, intStart))

End Function
''''code starts''''

You now have three functions to return the names reassigned from, to and by.
I've commented the code rather more than necessary so you can see how they
work. You'll see thet the 'from and 'to' functions are identical apart from
the constants declared as the assignment types. the 'by' function differs
because toy simply need to get the substring right up to the end of the
string, so don't need to compute the length of the name.

Save the module as something like basReAssignmentStuff.

So in a query in design view you'd put these expressions in the field rows
of blank columns:

From: ReassignFrom([action_description])
To: ReassignTo([action_description])
By: ReassignBy([action_description])

Ken Sheridan
Stafford, England

A couple of weeks ago, I posted to this group for help extracting the To and
By data from this field into two separate fields I could query and report on

cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY

I was given this and it worked GREAT:

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

I now have an Assigned To field that contains MARILYN MONROE and an Assigned
By that contains ROBERT KENNEDY.

The problem is that I don't understand how the function works and am trying
to modify it to do basically the same thing on another field, as shown below:

cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned by:
ELROY JETSON

I need essentially the same thing - the name after TO and the name after
Re-assigned by.

So far, I've worked out this:

To: Mid([action_description],InStr([action_description],"To:")+4)

It gives me

JUDY JETSON Re-assigned by: ELROY JETSON

I need to figure out how to lop off everything from Re-assigned by to the
end. I basically have to figure out where Re-assigned by starts (character
position), then subtract everything that comes after that character position
- but I'm just not getting it and could use a little more help.

Having the complete, working statement would be great. Having someone
explain the original solution to me in "layman's terms" would be even better
and make it possible for me to use this solution in the future.

This group is always very helpful and I appreciate any advice or explanation
you can provide.

 

[fredg]

A couple of weeks ago, I posted to this group for help extracting the To and
By data from this field into two separate fields I could query and report on

cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY

I was given this and it worked GREAT:

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

I now have an Assigned To field that contains MARILYN MONROE and an Assigned
By that contains ROBERT KENNEDY.

The problem is that I don't understand how the function works and am trying
to modify it to do basically the same thing on another field, as shown below:

cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned by:
ELROY JETSON

I need essentially the same thing - the name after TO and the name after
Re-assigned by.

So far, I've worked out this:

To: Mid([action_description],InStr([action_description],"To:")+4)

It gives me

JUDY JETSON Re-assigned by: ELROY JETSON

I need to figure out how to lop off everything from Re-assigned by to the
end. I basically have to figure out where Re-assigned by starts (character
position), then subtract everything that comes after that character position
- but I'm just not getting it and could use a little more help.

Having the complete, working statement would be great. Having someone
explain the original solution to me in "layman's terms" would be even better
and make it possible for me to use this solution in the future.

This group is always very helpful and I appreciate any advice or explanation
you can provide.

Where did you get
InStr([action_description],"Assigned Issue To:")+25
From?

I show the original reply to you as having been
InStr([CombinedNames],"Assigned Issue To:")+19
which is correct.
There are 18 characters in "Assigned Issue To:" and we need to add 1
more to bring us to the first letter in the wanted name.

Regarding:
cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned
by: ELROY JETSON

You need to have a different expression if you wish the second name
(Judy Jenson) to be extracted.

ReassignedTo:
Mid([OtherField],InStr([OtherField],"To:")+4,(InStrRev([OtherField],"Re-Assigned
by:")-1)-(InStr([OtherField],"To:")+4))

Make sure you change [[OtherField] to whatever the actual name is of
your field.

The above finds the position of the phrase "To:" within the string.
We then add 4 (the phrase "To:" is 3 characters, plus 1 to bring us to
the first character in the name Judy Jetson).

Next we find the position of the phrase "Re-assigned To:". We then
subtract 1 to take us top the last letter in the wanted name.
From that number we then subtract the original starting position of
the word "To:" (plus the above 4) to determine the number of
characters in the name.

In the below example,
cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned
by: ELROY JETSON

The word "To:" is located at position 42. Add 4 and the pointer is now
on the letter "J" in Judy.
To then determine how many letters to return, we first find the
position of "Re-assigned By:".
InStrRev will return the position of 58. From that we subtract 1 to
move us towards to last letter in the wanted name.
Now we have 57.
From that 57 we then subtract the starting position of the "J" in Judy
(which was 42 + 4)

57-46 = 11.
The query will then return the 11 letters starting at position 42.

The original By: expression from the previous post can remain the same
for the previous and this new string extraction as the phrase
"Assigned By:" is included within the phrase "Re-assigned By:"

Reassigned by: Mid([FieldName],InStrRev([FieldName],"assigned
by:")+13)

I hope this is clear to you.

 

[Anita Taylor]

Where did you get

InStr([action_description],"Assigned Issue To:")+25
From?

When I ran the original statement :

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


against this text:

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

I got:

e To: FRED FLI

I kept playing with the numbers until the actual name showed up, which may
have been wrong, but it kept working - which kind of feeds into my not
understanding the answer entirely.

I understand it a little more now and greatly appreciate your response on
this one - I haven't yet had a chance to try it, but now that I understand
the logic behind it a little better, I think I'll be in good shape!

A couple of weeks ago, I posted to this group for help extracting the To and
By data from this field into two separate fields I could query and report on

cs - Assigned Issue To: MARILYN MONROE Assigned by: JOHN KENNEDY

I was given this and it worked GREAT:

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

I now have an Assigned To field that contains MARILYN MONROE and an Assigned
By that contains ROBERT KENNEDY.

The problem is that I don't understand how the function works and am trying
to modify it to do basically the same thing on another field, as shown below:

cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned by:
ELROY JETSON

I need essentially the same thing - the name after TO and the name after
Re-assigned by.

So far, I've worked out this:

To: Mid([action_description],InStr([action_description],"To:")+4)

It gives me

JUDY JETSON Re-assigned by: ELROY JETSON

I need to figure out how to lop off everything from Re-assigned by to the
end. I basically have to figure out where Re-assigned by starts (character
position), then subtract everything that comes after that character position
- but I'm just not getting it and could use a little more help.

Having the complete, working statement would be great. Having someone
explain the original solution to me in "layman's terms" would be even better
and make it possible for me to use this solution in the future.

This group is always very helpful and I appreciate any advice or explanation
you can provide.

Where did you get
InStr([action_description],"Assigned Issue To:")+25
From?

I show the original reply to you as having been
InStr([CombinedNames],"Assigned Issue To:")+19
which is correct.
There are 18 characters in "Assigned Issue To:" and we need to add 1
more to bring us to the first letter in the wanted name.

Regarding:
cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned
by: ELROY JETSON

You need to have a different expression if you wish the second name
(Judy Jenson) to be extracted.

ReassignedTo:
Mid([OtherField],InStr([OtherField],"To:")+4,(InStrRev([OtherField],"Re-Assigned
by:")-1)-(InStr([OtherField],"To:")+4))

Make sure you change [[OtherField] to whatever the actual name is of
your field.

The above finds the position of the phrase "To:" within the string.
We then add 4 (the phrase "To:" is 3 characters, plus 1 to bring us to
the first character in the name Judy Jetson).

Next we find the position of the phrase "Re-assigned To:". We then
subtract 1 to take us top the last letter in the wanted name.
From that number we then subtract the original starting position of
the word "To:" (plus the above 4) to determine the number of
characters in the name.

In the below example,
cs - Re-assign Issue From: GEORGE JETSON To: JUDY JETSON Re-assigned
by: ELROY JETSON

The word "To:" is located at position 42. Add 4 and the pointer is now
on the letter "J" in Judy.
To then determine how many letters to return, we first find the
position of "Re-assigned By:".
InStrRev will return the position of 58. From that we subtract 1 to
move us towards to last letter in the wanted name.
Now we have 57.
From that 57 we then subtract the starting position of the "J" in Judy
(which was 42 + 4)

57-46 = 11.
The query will then return the 11 letters starting at position 42.

The original By: expression from the previous post can remain the same
for the previous and this new string extraction as the phrase
"Assigned By:" is included within the phrase "Re-assigned By:"

Reassigned by: Mid([FieldName],InStrRev([FieldName],"assigned
by:")+13)

I hope this is clear to you.