UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Extract From A String, Access 2010    
 
   
Larry Larsen
post Dec 7 2016, 08:50 AM
Post#1


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Seem to having a blank moment with regards to extracting "part" of a string..

My example is true reflection of the type of string I'm interested in..

Example string:

AAA.BBB.CCC.DDD.EEE.FFF.GGG.HHH

Each segment is separated by a "."

What I'm looking to do is extract the first (4) segments..
eg:

AAA.BBB.CCC.DDD

I can "split" the string up and then rejoin the (4) segments..

Looking to count the first (4) "." -1 as a single instruction if pos....

Some thing like:
X = Left(1,"to the forth "."-1)
iconfused.gif
Regards
Larry

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
BruceM
post Dec 7 2016, 09:22 AM
Post#2


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I assume the segments are not always the same length, or you could just use Left(TheString,15). Is it also the case that you are trying to stay away from using a UDF?

Maybe Split(TheString,".")(0) & "." & Split(TheString,".")(1) & "." etc.

I can't think of an easy way to get to the fourth dot, but if somebody posts a way to do that in a single expression I will be very interested in reading about it.
Go to the top of the page
 
bouncee
post Dec 7 2016, 09:23 AM
Post#3



Posts: 73
Joined: 5-August 05
From: Eastbourne, UK


Is it always 4 segments?

If so, then perform loop 4 times, setting counter from 1 to 4, using that and Instr on source to find the next '.' and building string as you go.
Go to the top of the page
 
gemmathehusky
post Dec 7 2016, 09:33 AM
Post#4


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


Hmm.

I can't see a way to do it with an inline function - pattern matching, maybe??
I also tried recursively (there must be a way), but in the end used this


'selects the string to the 'sectionth' instance of separator "sep'

so
slice("111.222.333.444.555.666",3,".")
returns
111.222.333

CODE
Function slice(testStrg As String, section As Long, sep As String) As String
Dim pos As Long
Dim snip As Long

    snip = 0
    pos = 1
    While snip < 4
        If InStr(pos, testStrg, sep) > 0 Then
            pos = InStr(pos, testStrg, sep) + 1
            snip = snip + 1
        End If
    Wend
    
    slice = Left(testStrg, pos - 2)
              
End Function

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
MadPiet
post Dec 7 2016, 09:40 AM
Post#5



Posts: 2,257
Joined: 27-February 09



Or similar...

CODE
Public Function FirstFourNodes(ByVal strInput As String) As String
    
    Dim varInput As Variant
    Dim i As Integer
    varInput = Split(strInput, ".")
    For i = 0 To 3
        If i = 0 Then
            FirstFourNodes = varInput(i)
        Else
            FirstFourNodes = FirstFourNodes + "." & varInput(i)
        End If
    Next i
    
End Function
Go to the top of the page
 
projecttoday
post Dec 7 2016, 09:43 AM
Post#6


UtterAccess VIP
Posts: 8,679
Joined: 10-February 04
From: South Charleston, WV


Larry and bouncee, the Left function only has 2 arguments. I think you're talking about the Mid function.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Larry Larsen
post Dec 7 2016, 09:45 AM
Post#7


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Many thanks for your thoughts.. the default is (4) but may change in the future..(+/-)

I have a number of UDF's that I could use, but it seem like maybe MS could provide Access with a function of some sort(Access Project Team).

Yes the length of each segment will vary....

To be able to move on I'm using this small routine...
CODE
  Dim db As dao.Database
    Dim rst As dao.Recordset
    Dim rs As dao.Recordset
    Dim sResult() As String
    Dim sText As String
  

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblFiles_Test")
    Set rs = db.OpenRecordset("tblFiles_Test2")

    CurrentDb.Execute "DELETE * FROM tblFiles_Test2", dbFailOnError

    rst.MoveFirst

    Do While Not rst.EOF
        sText = rst!FName
        sResult = Split(sText, ".")
        rs.AddNew
        rs!value1 = sResult(0) & " " & sResult(1) & " " & sResult(2) & " " & sResult(3)
        rs.Update

        rst.MoveNext
    Loop
    rst.Close
    db.Close


thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Larry Larsen
post Dec 7 2016, 09:46 AM
Post#8


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Guy's

Some great examples.. many thanks..
uarulez2.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
RAZMaddaz
post Dec 7 2016, 09:47 AM
Post#9


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Larry,

When all else fails, try this example. This uses a Form, where the User enters the criteria, then a click on a button runs a Query, NO VBA.

RAZMaddaz
Attached File(s)
Attached File  MulitpleDataInStr2.zip ( 21.48K )Number of downloads: 11
 
Go to the top of the page
 
Vince
post Dec 7 2016, 09:55 AM
Post#10



Posts: 32
Joined: 18-August 16
From: Bristol, UK


If it is always 4 then:
Left([field1],InStr(InStr(InStr(InStr([field1],".")+1,[field1],".")+1,[field1],".")+1,[field1],".")-1)
Go to the top of the page
 
Larry Larsen
post Dec 7 2016, 10:26 AM
Post#11


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Raz
Interesting.. many thanks..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
RAZMaddaz
post Dec 7 2016, 10:29 AM
Post#12


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


Hi Larry!

yw.gif

RAZ
Go to the top of the page
 
Larry Larsen
post Dec 7 2016, 10:29 AM
Post#13


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Vince

Many thanks.. can use that in a simple query at the mo....
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 12:33 AM