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

Extracting Data from Composite Field

P: n/a
On a bit of a no-brainer with this. I have a field containing composite
data which is imported from another system. Here's a sample:

TECH_ACT=2HL91002:TGT_START=12-NOV-07:CAM_CODE=AKJBA:DA=HL:WBS=DES071702:

I'd like to extract the element CAM_CODE=AKJBA but the string after the "="
sign can vary in length between rows. Does anyone have a code snippet in
their library that might do this ... or is there a built-in function that I
don't know about? I thought of maybe using InStr but with the string length
variable I don't think you can. I can't use the Mid function either because
other strings after their "=" sign can vary in length too. Any thoughts?

Many thanks.

Keith.

Jul 18 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
It looks like the string elements are delimited with a colon, and contain
two parts (left, equals, right.)

If that's the structure:
1. Use Split() to parse the string into an array at the ":" charcter.

2. Loop through the array elements to find the one Like "CAM_CODE=*"

3. Use Mid() to get the rest of that element.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...
On a bit of a no-brainer with this. I have a field containing composite
data which is imported from another system. Here's a sample:

TECH_ACT=2HL91002:TGT_START=12-NOV-07:CAM_CODE=AKJBA:DA=HL:WBS=DES071702:

I'd like to extract the element CAM_CODE=AKJBA but the string after the
"=" sign can vary in length between rows. Does anyone have a code snippet
in their library that might do this ... or is there a built-in function
that I don't know about? I thought of maybe using InStr but with the
string length variable I don't think you can. I can't use the Mid
function either because other strings after their "=" sign can vary in
length too. Any thoughts?

Many thanks.

Keith.
Jul 18 '07 #2

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...
It looks like the string elements are delimited with a colon, and contain
two parts (left, equals, right.)

If that's the structure:
1. Use Split() to parse the string into an array at the ":" charcter.

2. Loop through the array elements to find the one Like "CAM_CODE=*"

3. Use Mid() to get the rest of that element.
Hi Allen, thanks for responding. So if I have a function thus ...

Function libUnravel(strDesc As String) As String

Dim strElements, i As Integer
strElements = Split(":" & strDesc, ":")

For i = 1 To UBound(strElements)
Debug.Print strElements(i)
Next

End Function

.... the immediate window prints ...

TECH_ACT=2LM51001
TGT_START=01-OCT-07
CAM_CODE=AKJBA
DA=LM
WBS=DES072402

Is it possible to get each element in a different field by calling the
function from a stored query? I'm sure it must be but I don't know the
syntax.

Thanks again.

Keith.

Jul 18 '07 #3

P: n/a
Keith, I think this is what you are trying to do:

Function libUnravel(strDesc As String, ByVal n As Integer) As Variant
Dim varElements As Variant
Dim strReturn As String
Dim lngPos As Long

n = n - 1
varElements = Split(strDesc, ":")
If IsArray(varElements) Then
If n >= LBound(varElements) And n <= UBound(varElements) Then
lngPos = InStr(varElements(n), "=")
If lngPos 0 And lngPos < Len(varElements(n)) Then
strReturn = Mid$(varElements(n), lngPos + 1)
End If
libUnravel = varElements(n)
End If
End If

If strReturn <vbNullString Then
libUnravel = strReturn
Else
libUnravel = Null
End If
End Function

If your field is name MyField, then you could make an Append/Update query,
and type an expression like this into the field row:
libUnravel([MyField],1)
That should return whatever's after the first equal sign.
Use 2 for the 2nd one.

Add Trim() if you need to get rid of spaces, i.e.:
strReturn = Trim$(Mid$(varElements(n), lngPos + 1))
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...
>It looks like the string elements are delimited with a colon, and contain
two parts (left, equals, right.)

If that's the structure:
1. Use Split() to parse the string into an array at the ":" charcter.

2. Loop through the array elements to find the one Like "CAM_CODE=*"

3. Use Mid() to get the rest of that element.

Hi Allen, thanks for responding. So if I have a function thus ...

Function libUnravel(strDesc As String) As String

Dim strElements, i As Integer
strElements = Split(":" & strDesc, ":")

For i = 1 To UBound(strElements)
Debug.Print strElements(i)
Next

End Function

... the immediate window prints ...

TECH_ACT=2LM51001
TGT_START=01-OCT-07
CAM_CODE=AKJBA
DA=LM
WBS=DES072402

Is it possible to get each element in a different field by calling the
function from a stored query? I'm sure it must be but I don't know the
syntax.

Thanks again.

Keith.
Jul 18 '07 #4

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:46***********************@per-qv1-newsreader-01.iinet.net.au...
Keith, I think this is what you are trying to do:
Thanks very much Allen, much appreciated.

Keith.

Jul 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.