ms access sql expression to seperate data

[kevinh2320]

I have a column in my MS ACCESS query called Field1 with data similar to what's shown below. I'm looking for SQL expressions to separate this information into 3 separate AS columns. So, looking at the top row that would be .00 then .00 and then BILLING ACTIVATED. Thanks!

Field1

.00 .00 BILLING ACTIVATED

.00 .00 BILLING ACTIVATED

.00 6,500.00 BILLING ACTIVATED

.00 .00 CLOSED

5,800.00 .00 BILLING ACTIVATED

.00 325.00 REACTIVATED

.00 .00 LEASE TERMINATED

.00 .00 RELINQUISHED

.00 .00 PENDING/SUSPEND/I

 

[QuietRiot]

SELECT SplitString(column, ' ', 0), SplitString(column, ' ', 1), SplitString(column, ' ', 2)

Public Function SplitString(str As String, delimiter As String, column As Integer) As String
    Dim strArr() As String
    strArr = Split(str, delimiter)
    
    If column > 1 Then
        For i = 2 To UBound(strArr)
            SplitString = SplitString & strArr(i) & " "
        Next
        
        SplitString = Trim(SplitString)
    Else
        SplitString = strArr(column)
    End If
End Function

 

[Joe4]

For a non-VBA solution, you could use Access Query String Functions, like this:
FirstField: Trim(Left([Field1],InStr([Field1]," ")))
SecondField: Trim(Left(Mid([Field1],Len([FirstField])+2),InStr(Mid([Field1],Len([FirstField])+2)," ")))
ThirdField: Trim(Mid([Field1],InStr(InStr([Field1]," ")+1,[Field1]," ")))

Though I like the use of a User Defined Function too (a bit cleaner)!

 

[kevinh2320]

Got it working! Thanks for your help!

 

[Joe4]

Not sure which solution you went with, but glad you got it working!