pull individual data out of one field without pulling all data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to pull delimited data that is contained within one field in my
database. Is there a way that I can isolate and pull one portion of the
delimited data without pulling all the data in the field?
 
Sounds like a poor table structure that has multiple data in a single field.

Give an example of your data. What are the delimiters? Are they consistent?
 
You can write a VBA function that gets the the nth item using the split
function.
For example the following may do what you want.

Save it as a function in a module and then call it in the query.

Field: getSection([SomeField], ";",3)

that would return the third segment of a string that is divided by
semi-colons.


Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function
 
Back
Top