Extract text from a field in access (1 Viewer)

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:37
Joined
Sep 12, 2006
Messages
15,614
it looks to me like the OP wants the right part within the brackets, assuming the left part is "09"
I don't have time to fiddle with it now, but I would

a, find the position of "(09"
b. find the position of ")" after the above.
c. return the string between these two positions, adjusting for the start character.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:37
Joined
Sep 21, 2011
Messages
14,048
I thought he said only numbers that start with 09? Maybe I misunderstood what he was asking. I need to learn more on those regex patterns as that can really be quite powerful.
I read it as having to find the "09" in amongst all that data in the field?, then find the end.?
Regex is very cool, but getting the pattern correct is a little difficult for me. :(
Fortunately I do not have to use it these days. :)
 

Saphirah

Active member
Local time
Today, 06:37
Joined
Apr 5, 2020
Messages
163
On further review it looks like they are all 10 characters long and will always end with two numbers at the end
Then the simplest code i can come up with would be:
Code:
inputStr = "CUT 2 09GN20T-01 LEADS"
result = Mid(inputStr, InStr(inputStr, "09"), 10)
Be carefull though. If it is possible that a 09 could appear somewhere before your desired result, a regex pattern would be better.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:37
Joined
Feb 19, 2002
Messages
42,981
When you have to extract strings like this, you not only need to know how they start but either how long they are or how to determine the end.
The best solution of all is to NEVER, EVER store data like this but we don't always have control over our input and non-programmers don't have any idea how difficult it can be to parse these strings.
 

Lucky33

Registered User.
Local time
Yesterday, 22:37
Joined
Sep 3, 2011
Messages
57
Try this in a query:
Extracted: Mid([PNum],InStr(1,[PNum],"09"),10)
Equivalent to this:
SELECT TblTest.PNum, Mid([PNum],InStr(1,[PNum],"09"),10) AS Extracted
FROM TblTest;
it should work for 10 numbers


QueTest

PNumExtracted
CUT (09GN20T-01) 73" LONG09GN20T-01
(09GN20T-01)CUT 6.50" - STRIP09GN20T-01
CUT 2 09GN20T-01 LEADS09GN20T-01
(09BN22R-01) CUT 3.5"09BN22R-01
 
Last edited:

Mike Krailo

Well-known member
Local time
Today, 01:37
Joined
Mar 28, 2020
Messages
1,030
Yes, that's essentially what Saphirah said in post #24.
 

Cotswold

Active member
Local time
Today, 05:37
Joined
Dec 31, 2020
Messages
521
Hi xmattxman, My first thought on this is to use string handling as the solution. However, I found the suggestions on using Patterns and Like interesting, and have to admit not having using them. Clearly I have not read the Language Reference as well as I thought!

Presumably you'll be taking the long code from Table so all you need to do is to pass that variable to the Function GetShortCode() at any point in your code. (Presuming of course that you place the Function in your own library for the application in Modules.)

You could also update a table using an Update Query by calling the function in the UpdateTo section in the Query using the LongCode in the table as the parameter.

The following Function will return the part of the code required. Clearly from the other posts there is never one way in Access, or indeed any language to solve a problem. It is always the method that you prefer to use,

Function GetShortCode(LongerCode As String) As String
'
Dim mLongCode, mCodeCut As String
Dim mLenSC, mPos09, mLenCut As Integer
'
mLongCode = LongerCode
mLongCode = Trim(mLongCode)
mLenSC = Len(mLongCode)
mPos09 = InStr(mLongCode, "09")
mLenCut = mLenSC - mPos09 + 1
mCodeCut = Right(mLongCode, mLenCut)
'
For X = 1 To mLenCut
If Mid(mCodeCut, X, 1) = Space(1) Or Mid(mCodeCut, X, 1) = ")" Then
GetShortCode = Left(mCodeCut, (X - 1))
X = mLenCut + 1
End If
Next X
'Returns short code
'!NOTE! No error checking or data validation exists here, needs to be added
'
End Function ' GetShortCode()
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:37
Joined
May 7, 2009
Messages
19,169
The following Function will return the part of the code required
test this string to your function:

"(09(09BN22R-01) CUT 3.5"""

result is:
"09(09BN22R-01"

using my RegExp function, the result is:
"09BN22R-01"
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:37
Joined
Sep 21, 2011
Messages
14,048
Uh guys, OBP has already given o/p a working db in the cross posted thread.?
 

Users who are viewing this thread

Top Bottom