Query to extract different values from variable string ?

[DBS]

I use the following string to capture/record notes in a field:

String: AA-AnyTwoLettersHere(ddShortMonthYYYY)
Example: AA-BB(05May2012)

I want to extract
a) the two letters following "AA-"
b) the date in brackets.

Problem is the field may also contain similar variations of the string, such as:

AA-BB(05May2012_SomeTextHere) - Don't want to extract the text following the date

AA-BB/05May2012 - Don't want to extract anything here because it follows a different convention

To make it more complicated, if the string is found more than once in the field I want to only extract the two bits of info from the LAST instance of it in the field.



Does anyone think they can help me?

[pbaldy]

Well, I'll get it started, though it looks like it could get messy. The 2 letters are easy, you can use the Mid() function. You can also use the Mid() function along with the InStr() function to find the "(". Since the "(" may or may not be there, probably an IIf() to only pull the value if it is.

If you're saying the whole bit can repeat in the field, it get's messier. If the data supports it, You can use InStrRev() to find the last occurrence of either the "-" or "(" and pull from there. If the text in the "SomeTextHere" area could contain either of those, all bets are off.