You can use a custom VBA function like the one posted below to get the parts.
In your query you would have three calculated fields that would look like
Field: String1: getSection([StringColumn,";",1)
You could add the Trim function to that to strip off any leading or trailing
spaces.
Field: String1: Trim(getSection([StringColumn,";",1))
Copy the following into a VBA module and save the module with a name other
than getSection.
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
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
BusyProfessorFromBremen wrote:
> Hey @ all,
>
> at the moment there is one table existing which contains one big string in
> one column. I would like to cut this string into its components and by doing
> so generate smaller strings. These smaller strings should be copied into
> another table where the colums heading stand for one string. Please see below.
>
> State of the art:
>
> ID STRINGCOLUMN
> 123 First, Last ; Second, Third; Forth, Fifth
> 321 First, Last ; Seventh, Third; Sixth, Fifth
>
> Objective:
>
> ID STRING1 STRING2 STRING3
> 123 First, Last Second, Third Forth, Fifth
> 321 First, Last Seventh, Third Sixth, Fifth
>
> Any help will be greatly apreciated! Thank you very much in advance.
>
> malte
>
|
Similar Threads
