[AccessD] Extract Strings from Long Test String

    Dim txt As String
    Dim x As Variant
    Dim i As Long


    txt =
"[LHDMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953200]/[795320
7]/[1N756A]"

   	txt = Replace(str, "[", "")
   	txt = Replace(str, "]", "")
    
	x = Split(txt, "/")

    For i = 0 To UBound(x)
       Debug.Print x(i)
    Next i

Quickly typed out and not tested but should put you on the correct path.

Barry





-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hewson, Jim 
Sent: Wednesday, July 09, 2008 1:34 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Extract Strings from Long Test String

Thanks for the quick response,
It's probably the way I explained it.  This isn't quite right.
I need the text between the brackets.
For example:
Original string:
[LHDMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953200]/[7953207
]/[1N756A]")
 
LH-DMSP    AN/FMQ-7    7361002-2    7361799    7361795   953   7953200
7953207   1N756A
 
I need to be able to use the extracted text in separate fields in a query.
I'll need to filter some out, others I'll need to sort using one or more of
the new "fields."
 
Jim 
 
  
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, July 09, 2008 12:47 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Extract Strings from Long Test String
 
If I understand the question right, you should be able to do this with just
a couple of calls to Replace()...
 
Function fixFullPath(str As String) As String
    str = Replace(str, "[", "")
    str = Replace(str, "]", "")
    fixFullPath = str
End Function
 
And here's a test run.,,
 
?
fixFullPath("[LHDMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953
200]/[7953207]/[1N756A]")
LHDMSP/AN/FMQ-7/7361002-2/7361799/7361795/953/7953200/7953207/1N756A
 
HTH
 
Lambert
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hewson, Jim 
Sent: Wednesday, July 09, 2008 12:53 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Extract Strings from Long Test String
 
I'm trying to extract several components from a text string. The string was
created from a legacy system download into Excel. The text string is located
in one field of a table that was imported into the database.
The string is called a "Full Path" because it represents the location where
the data was located in the legacy system.  Each import could have several
thousand rows.
 
Below are samples of the field (watch wrap):
[LHDMSP]/[AN/FMQ-7]/[7361002-2]/[7361799]/[7361795]/[953]/[7953200]/[7953207
]/[1N756A]
[LH
DMSP]/[AN/FRR-95]/[317400]/[317359]/[624459]/[317298-1]/[PTM15-1.5D]/[23
5-7053P334]
[LH MWD]/[AN/FPS-123 (V)
1]/[957157-1]/[953252-1]/[584786-1]/[STM5-24]/[586371-5]
[LH SCS]/[AN/FPS-85]/[2054300-0501]/[Receiver]/[No Number
(F20)]/[2052660-0502]/[2018219-0501]/[2020306-0501]/[2055347-0506]/[2018
880-0001]/[1N752A]
 
What I need to do is extract the strings between the brackets (i.e. "[",
"]/[","]") for each "Full Path" and be able to use them in queries.
There should be a minimum of five sets and there could be as many 15 sets of
brackets.
 
I have been able to get the first two but after that nothing seems to work.
 
Any suggestions?
 
MTIA
 
Jim
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

############################################################################
####

This email transmission contains information from NCI Information Systems,
Inc. 
that may be considered privileged or confidential and is intended solely for
the 
named recipient.  If you have received this message in error, please contact
the 
sender immediately and be aware that the use, copying or dissemination of
this 
information is prohibited.

############################################################################
####
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com