Query Extract text before each comma

[JMichaelM]

Looking for help on a query to help save me time trying to figure it out. The expression needs to break up query into different columns. [Department, Unit, Division, Entity] Field has four sets of data in one recordset. Need to extract Department into separate column, unit in separate column, division in separate column. Probably simple but only (8) hrs in a day. Thanks

 

[CJ_London]

you need to create a simple function in a module

Function Splitstr(s as string, element as integer) as string
Splitstr=split(s,",")(element)
end function

then in your query

Dept:splitstr(myfield,0)
Unit:splitstr(myfield,1)
etc

 

[jdraw]

See this info on Split()

 

[JMichaelM]

Got the first split. New: Left([Entity / Division / Function / Unit],InStr([Entity / Division / Function / Unit],", ")-1)

 

[JMichaelM]

Got the last one now just need the 2nd and 3rd position. Unit: Mid([Entity / Division / Function / Unit],InStrRev([Entity / Division / Function / Unit],", ")+1)

 

[jdraw]

Can you show us 5-10 sample records?

 

[JMichaelM]

Its all different size text split with a comma.

 

[CJ_London]

@JMichaelM - have you read all the responses? the same solution (which is not the road you are going down) has been provided in posts 2 and 3

 

[JMichaelM]

Yes I did thanks.

 

[CJ_London]

So what is wrong with them as a solution?

 

[JMichaelM]

I'm trying to avoid building a function when it should be able to be done with a query expression. If I can't figure it out I will build function.

 

[jdraw]

What about some sample data--we don't care if it's real or varying sizes...

PorkyPig,this is another piece of the puzzle,8,the last part of this expression to be parsed into separate fields

Split will give you:

PorkyPig
this is another piece of the puzzle
8
the last part of this expression to be parsed into separate fields

Good luck

 

[JMichaelM]

Sample data in Field:

[SHKI, Marketing, Home Equity, Operations]

 

[jdraw]

Here's some vba to consider Split()

Sub sampleSplit()
    Dim str As String
    Dim i As Integer
    Dim parts As Variant
    str = "[SHKI, Marketing, Home Equity, Operations]"
    parts = Split(str, ",")
    For i = 0 To 3
        Debug.Print i; parts(i)
    Next i
End Sub

Output is:

 0 [SHKI
 1  Marketing
 2  Home Equity
 3  Operations]

If you get this data in a file (CSV), you could import the data into a table with a External Data wizard.
If you want to process record by record with a function, you could use a recordset and addnew; update to add data to fields and records to your table.

Good luck.

 

[CJ_London]

I'm trying to avoid building a function when it should be able to be done with a query expression

I'm all in favour of doing that and it is my preferred route if you look at my other posts on similar requirements. But I have been doing this for many years, so believe me, I wouldn't have recommended a vba function unless I thought it was the better way.

 

[jdraw]

I agree with CJ.
Perhaps you can describe where and how the "expression" gets to you. If it comes to you as a file, there may be other options.

Good luck.

 

[JMichaelM]

I was able to build all four new columns with expressions. Thanks

 

[jdraw]

It might be useful to others if you would post your solution.

 

[JMichaelM]

Problem: 4 text in field seperated by comma
Objective: Separate each block of text into (4) columns

Sample data in Field:
Entity/Department/Function/Unit
[SHKI, Marketing, Home Equity, Operations]

Solution:

Column 1:

Entity: Left([Entity/Department/Unit/Function],InStr([Entity/Department/Unit/ Function],", ")-1)

Create String for Column 2 and Column 3:

String: Mid([Entity / Department / Function / Unit],InStr([Entity / Department / Function / Unit],", ")+1)

Column 2:


Department: Left([String],InStr([String],", ")-1)



Column 4
Unit: Mid([Entity / Department / Function / Unit],InStrRev([Entity / Department / Function / Unit],", ")+1)

Create String for Column 3:
FunctionString: Mid([String],InStr([String],",")+1)


Function: Left([FunctionString],InStr([FunctionString],", ")-1)

 

[JMichaelM]

Solution to seperate column with 3-4 words seperated by comma into 4 columns:
Sample Field Record set
[CGY, Norad, Tech, Jones]

Left([Entity / Department / Function / Unit],InStr([Entity / Department / Function / Unit],", ")-1) AS Entity,

Left([String],InStr([String],", ")-1) AS Department,

Mid([Entity / Department / Function / Unit],InStr([Entity / Department / Function / Unit],", ")+1) AS [String],

IIf(InStr(Nz([FunctionString]),", ")<2," ",Left(Nz([FunctionString]),InStr(Nz([FunctionString]),", ")-1)) AS Function,

Mid([Entity / Department / Function / Unit],InStrRev([Entity / Department/ Function / Unit],", ")+1) AS Unit,

Mid([String],InStr([String],",")+1) AS FunctionString