Substring in MS Access

[ay1man4]

I need to extract part of text where I can find the second frequency of predefined character.
Example:
Text is "BTSM:0/BTS:0/TRX:1"
I need function whivh return from the previous text only "BTSM:0/BTSM"
if the text changed to for example "BTSM:32/BTS:0/TRX:1
it should return "BTSM:32/BTS:0"

I hear that in sql there is Substr function which can do that but in access i try to use Mid but it needs length which is variable...
what is the solution??

[Rabbit]

I need to extract part of text where I can find the second frequency of predefined character.
Example:
Text is "BTSM:0/BTS:0/TRX:1"
I need function whivh return from the previous text only "BTSM:0/BTSM"
if the text changed to for example "BTSM:32/BTS:0/TRX:1
it should return "BTSM:32/BTS:0"

I hear that in sql there is Substr function which can do that but in access i try to use Mid but it needs length which is variable...
what is the solution??

I have no idea what you mean by "find the second frequency of predefined character"

Are you saying that you want to return everything before "/TRX:1"?
If "/TRX:1" only ever occurs once and always at the end then you can use the replace function, rather than a convoluted find position and then substring.

[missinglinq]

As Rabbit hinted, your post is somewhat fuzzy, and your two examples are not consistent! You state:

"BTSM:0/[b]BTS:0[/b]/TRX:1"  is starting string
"BTSM:0/[b]BTSM[/b]" 		   is what you want

"BTSM:32/[b]BTS:0[/b]/TRX:1"  is stating string
"BTSM:32/[b]BTS:0[/b]"			 is what you want

Is your object to remove everything from the second slash?

If so, are the number of characters after the second slash always the same? LEt us know so we can help you.

Welcome to TheScripts!

Linq ;0)>

[ay1man4]

Thank you very much for your reply..

Is your object to remove everything from the second slash?

Yes, that exactly what I mean. (Forgive me for my bad english!!)

If so, are the number of characters after the second slash always the same?

No, its not constant because some times I can find data like this:

BTSM:0/BTS:1/TRX:1/CHAN:0

so what I need is everything before the second slash what ever its length.

[missinglinq]

Okay, this will do it. Where YourText is the starting string and NewText is the ending string

[CODE=vb]LeftHalf = Left(YourText, InStr(YourText, "/"))
RemainingText = Right(YourText, Len(YourText) - InStr(YourText, "/"))
RightHalf = Left(Remainingt ext, InStr(Remaining text, "/") - 1)
NewText = LeftHalf & RightHalf
[/CODE] BTSM:0/BTS:1/TRX:1/CHAN:0 becomes
BTSM:0/BTS:1

and

BTSM:32/BTS:0/TRX:1 becomes
BTSM:32/BTS:0

Line # 1 grabs everything up to and including the first slash mark.
Line # 2 grabs everything remaining in the string
Line # 3 repeats the operation of Line # 1 on the results of Line # 2, except it omits the trailing slash mark.
Line # 4 concatenates the results from # 1 and # 3

Linq ;0)>