473,326 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Extracting Data from Composite Field

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
4 2304
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Redd | last post by:
The following is a technical report on a data modeling project that was recently assigned to me by my professor. I post it so that anyone else who is studying databases and data modeling can have...
0
by: Mike Coppinger | last post by:
I have a problem that has raised a couple of questions. SITUATION: I have a table called pr_persona that has a composite key comprising pr_persona_db CHAR(2) pr_persona_id INT(11) auto...
1
by: rufus | last post by:
Hi, I was wondering how a DataGrid can handle a DataSet with a composite key. How would I update 1 row in a database having a composite key? A possible solution would be to concatenate my...
1
by: v0lcan0 | last post by:
Any help on extracting the time part from the datetime field in SQL database. even though i had entered only the time part in the database when i extract the field it gives me only the date...
27
by: gRizwan | last post by:
Hello all, We have a problem on a webpage. That page is sent some email data in base64 format. what we need to do is, decode the base64 data back to original shape and extract attached image...
18
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many...
1
by: lists | last post by:
When using the (tbl).field notation for selecting a specific field from a composite field then the query returning the field is executed once per field. An example is giving below. The runtime is...
2
by: bobby_b_ | last post by:
I have a table where fields 1 and 2 make up the primary key. Because of this, I have a unique composite index on fields 1 and 2 (as required by DB2). Now my question is: Fields 1 and 2 are also...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.