Extracting Partial Data from one field into another

[jessieh]

I have a text field with a lot of different data in it.
Here is an example:
SERV: PS NJ////SAR #155097//OH #08468////SAR #158097//OH #08469////SAR
#158098//OH #08470////SAR #158099//OH #08471////SAR #158100//OH #08472

The name of the field where this data is stored in Notes4. I want to
put the data into a field called Numbers

I want to be able to extract out the Member numbers which are the
characters
"SAR #??????"

Ideally It would duplicate the record and put each different number
into each duplicated record, but I wouldn't mind if pulled all five
instances into one field. I can seperate them manually later.
Sometimes, there is only one instance and other times, there can be up
to 8 in the field

So far, I have tried an update query with [Notes4] Like "SAR #???????"
This just put zeros in the field.

I am fairly new, so if anyone can help in as plain english as possible,
it would be greatly appreciated.

Thanks

JessieH

 

[Albert D.Kallal]

The code to parse this out could be:

Public Function MyPar(v As Variant) As Variant

Dim vList As Variant
Dim i As Integer

Dim result As String
vList = Split(v, "sar #")

For i = 1 To UBound(vList)
If result <> "" Then result = result & ","

result = result & Trim((Split(vList(i), "/")(0)))
Next i

MyPar = result
End Function

Sub test1111()

Dim t As String

t = "SERV: PS NJ////SAR #155097//OH #08468////SAR #158097//OH
#08469////SAR #158098//OH #08470////SAR #158099//OH #08471////SAR
#158100//OH #08472"

Debug.Print MyPar(t)
--->>> you get 155097,158097,158098,158099,158100

End Sub

So, I would run the code to produce the "text" field. I would then use
simlyar code..and parse this out to a another table..or whatever....

You can use the above function in a udpate query to "move" the reuslt to
another field....

 

[jessieh]

Mr. Kallal,

Thank you for your reply. I have about 100,00 records, with different
numbers for each record. Is there a way to have it look for the
characters "SAR #" followed by the next 6 digits and move them out to
another field.

 

[John Nurick]

See my response to your message in the Queries newsgroup
(microsoft.public.access.queries).

Next time, please don't post the same question separately to multiple
groups. Normally it's only necessary to post to one group.