By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

Substring in MS Access

P: 2
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??
Aug 12 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,389
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.
Aug 12 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
As Rabbit hinted, your post is somewhat fuzzy, and your two examples are not consistent! You state:

Expand|Select|Wrap|Line Numbers
  1. "BTSM:0/BTS:0/TRX:1"  is starting string
  2. "BTSM:0/BTSM"            is what you want
  3.  
  4. "BTSM:32/BTS:0/TRX:1"  is stating string
  5. "BTSM:32/BTS:0"             is what you want
  6.  
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)>
Aug 12 '07 #3

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. BTSM:0/BTS:1/TRX:1/CHAN:0
Expand|Select|Wrap|Line Numbers
  1. so what I need is everything before the second slash what ever its length.
Aug 13 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
Okay, this will do it. Where YourText is the starting string and NewText is the ending string

Expand|Select|Wrap|Line Numbers
  1. LeftHalf = Left(YourText, InStr(YourText, "/"))
  2. RemainingText = Right(YourText, Len(YourText) - InStr(YourText, "/"))
  3. RightHalf = Left(Remainingtext, InStr(Remainingtext, "/") - 1)
  4. NewText = LeftHalf & RightHalf
  5.  
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)>
Aug 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.