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.