ms access sql expression to seperate data

kevinh2320

New Member
Joined
May 13, 2016
Messages
32
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

Well-known Member
Joined
May 18, 2007
Messages
1,053
SELECT SplitString(column, ' ', 0), SplitString(column, ' ', 1), SplitString(column, ' ', 2)

Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
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)!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
Not sure which solution you went with, but glad you got it working!
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top