InstrRev - Strip out end of text, where multiple parenthesis

I need to compare 2 fields if field 2 is null then look at field 1 (text string) and strip out the end of text where data is within ( ).  However the text string can contain multiple sets of parenthesis, I am only interest in the last set at the end of the text string.  How do I strip out the data between the Last Parenthesis?

Sample
Courses for the Employee (members) Must take (TR1092)
Results should be:  TR1092

Note that the length of the data between the last () can vary  looking for a function to use with a query..

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
mbizupCommented:
You need to define your function using a datatype, and then explicitly set your function return by using the function's name:

Function CleanUPTxt(s As String) As String  '<--- define it as string
If IsNull(s) = False Then
    On Error GoTo cont:
  s = Mid(s, InStrRev(s, "(") + 1, InStrRev(s, ")") - InStrRev(s, "(") - 1)
  Debug.Print s
  CleanUPTxt = s  '<---- Set the function's return value.
cont:
End If

End Function
0
 
iandianCommented:
You should use the mid function.
http://msdn.microsoft.com/en-us/library/05e63829%28VS.80%29.aspx

something like this:

if isnull(field2) then
  dim result = mid(field1, instrrev(field1, "("), instrrev(field1, ")"))
end if

Open in new window

0
 
mbizupCommented:
This will do it.  I'm assuming you can have characters after the last  ")"

mid(S, instrrev(s,"(") + 1, instrrev(s,")")- instrrev(s,"(")-1)
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Karen SchaeferBI ANALYSTAuthor Commented:
No characters after last )

Still getting the data within the parenthesis and I need those stripped off also.

1st try's Results currently getting:

(9HBGMDFODC)
(TR007262)
(TR007262)
(FINEST255)
(FINEST255)

2nd attempt gives error:
Invalid procedure call or argument (Error 5)


Function CleanUPTxt(s As String)
If IsNull(s) = False Then
  s = Mid(s, InStrRev(s, "("), InStrRev(s, ")"))
  Debug.Print s
End If

Or 

Function CleanUPTxt(s As String)
If IsNull(s) = False Then
  s = Mid(s, InStrRev(s, "(") - 1, InStrRev(s, ")") - InStrRev(s, "(") - 1)
  Debug.Print s
End If

End Function

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Also need to handle if there are no parenthesis then skip it an move to the next
K
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
This what I have so far, however the code runs but does not return an actual value - what am I missing.

I need it to display the CourseNO if not null or if isnull then the stripped out Course number from the Title field.

SELECT TL_CourseList.[Ilp Learning Title], TL_CourseList.[Ilp Learning Cd], CleanUPTxt([Ilp Learning Title]) AS CourseNo
FROM TL_CourseList LEFT JOIN TL_Training ON TL_CourseList.[Ilp Learning Cd] = TL_Training.TrainingCourseNo
GROUP BY TL_CourseList.[Ilp Learning Title], TL_CourseList.[Ilp Learning Cd], CleanUPTxt([Ilp Learning Title]);


Function CleanUPTxt(s As String)
If IsNull(s) = False Then
    On Error GoTo cont:
  s = Mid(s, InStrRev(s, "(") + 1, InStrRev(s, ")") - InStrRev(s, "(") - 1)
  Debug.Print s
cont:
End If

End Function

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks that did the trick once I included

IIf(IsNull([Ilp Learning Cd]),CleanUPTxt([Ilp Learning Title]),[Ilp Learning Cd]) into the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.