Re: Extracting Data from Composite Field

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" <here@xxxxxxxxx> wrote in message
news:469e212d$1_1@xxxxxxxxxxxxxxxxxxxxxxxxx
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message news:469e175b$0$12845$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.

.

Relevant Pages

  • Re: Word 2007 - Daten aus txt-Datei in Textbox einlesen
    ... strProjektKomplett As String ... Dim strInhalt As String ... Dim lngPos As Long ... Dim strTemp As String ...
    (microsoft.public.de.word.vba)
  • Re: Getting data from FileDialog
    ... Dim strPart As String ... Dim intLenofExt As Integer ... Dim lngPos As Long ... ' do not include the file ext in the filename ...
    (microsoft.public.access.formscoding)
  • Re: InStr search counter
    ... > Public Function CountPhrase(ByVal Search As String, ... > Dim strSearch As String ... > Dim strTarget As String ... > Dim lngPos As Long ...
    (microsoft.public.vb.general.discussion)
  • Re: Opentextfile TriStateMixed
    ... Public Function GetFileEncoding(sFileName As String) As String ... Dim FileNum As Integer ... Dim bytArrayAs Byte, lngArraySize As Long, lngBytes As Long, lngPos ...
    (microsoft.public.vb.general.discussion)
  • Re: Find nth instance of a character in a string
    ... Function LastPosition(ByVal strInput As String, ByVal strChars As String) As Long ... Dim lngPos As Long ...
    (microsoft.public.excel.misc)