Hi folks
I need a formula to use in a MS Access query to extract a portion of text from a text string, where the portion lies between 2 delimiting characters (#)
The field length is not always the same.
I tried the "MID", "Instr", "LEN" functions, but nothing successful yet
:confused::confused::(

what do you have that is not working??

If all else fails make a seperate (module) function and do it step by step instead of doing it all in 1 go.

edit: Welcome to AWF

Let's say we have a string called strInput containing:
123456789#banana#987654

InStr(strInput , "#") returns 10, so we know the position of the first instance of #

InStrRev(strInput , "#") returns 17, so we know the position of the last instance of #

So InStrRev(strInput , "#")-InStr(strInput , "#") gives you (more or less) the length of the bit you want to extract

Mid(strinput, InStr(strinput, "#"), InStrRev(strinput, "#") - InStr(strinput, "#")) returns '#banana' - so we need to start from one position further on:

Mid(strinput, InStr(strinput, "#")+1, InStrRev(strinput, "#") - InStr(strinput, "#")) returns 'banana#' - so we need to make the extracted portion one character shorter:

Mid(strinput, InStr(strinput, "#")+1, InStrRev(strinput, "#") - InStr(strinput, "#")-1) seems to work...

However, if you pass it: 123456789#banana#0000#apple#987654, it will return 'banana#0000#apple' and if you pass it a string with less than two # characters in it, it falls over...

another way is to use split (after A97)

split(string,"#") will give you an array of three strings

1) the part before the first #
2) the bit inbetween
3) the bit after the second #

so you can easily just get the middle bit directly from the array.

not sure offhand of the exact syntax, as I am not in front of an A2003 machine

To follow on from Mikes post it may be better to create a function in a standard module for this exercise

Public Function ExtractText(AnyText As String, Symbol As String) As String

Dim FirstInst As Integer
Dim LastInst As Integer
Dim strStringLen As Integer

'First check for the existance of the symbol

If InStr(AnyText,Symbol) = 0 Then
ExtractText = AnyText
Exit Function
End If

'Next check to see if there is only one occurance of the symbol

FirstInst = InStr(AnyText,Symbol)
LastInst = InStrRev(AnyText,Symbol)
If FirstInst = LastInst Then
ExtractText = Mid(AnyText,FirstInst+1)
Exit Function
End If
'Calculate the length of the string between the first instance and last instance of the symbol

strStringLen = ((LastInst-1) - (FirstInst+1))

ExtractText = Mid(AnyText,FirstInst+1,strStringLen)

End Function

To Test
Str = ExtractText("abc#123#def","#") ' returns 123

Like Mike said though multiple instances of the symbol only show the outer string.

Str = ExtractText("abc#123#def#345#xyz","#") ' returns 123#def#345

If have included the symbol (#) in the function should the symbol ever need to be changed.

David

David