converting text field to 3 columns

Hello experts,
This is probably easy peazy for some of you, but I busted the brain trust.
I have one field that needs to be broken into 3 columns in a query.
The cell contains data formatted as thus;
HD_Data
[Category (class)]
VMAC > WMS > Package - Small Package (UPS/FEDEX/Custom)
VMAC > WMS > ePMU
VMAC > WMS > ePMU
VMAC > WMS

VMAC > Inventory
VMAC > EDI
VMAC > Inventory > Paper - Inventory / Purchasing / Receiving / Usage
VMAC > Inventory > Ink Inventory / Purchasing / Receiving / Usage
VMAC > Finance > General Ledger
VMAC > Finance > Account Payable
VMAC
Need to have it look like this, 3 fields, containing the part or the blanks
HD_Data
[Assigned to - Group]      [Function]    [Issue]
VMAC      WMS      Package - Small Package (UPS/FEDEX/Custom)
VMAC      WMS      ePMU
VMAC      WMS      ePMU
VMAC      WMS      WMS
            
VMAC      Inventory      
VMAC      EDI      
VMAC      Inventory      Paper - Inventory / Purchasing / Receiving / Usage
VMAC      Inventory      Ink Inventory / Purchasing / Receiving / Usage
VMAC      Finance      General Ledger
VMAC      Finance      Account Payable
VMAC
KjBleauAsked:
Who is Participating?
 
peter57rCommented:
In your query you create 3 new fields similar to this..

Select
split([Category(Class)]& ">", ">")(0) as AssignedToGroup,
split([Category(Class)]& ">", ">")(1) as Function,
split([Category(Class)]& ">", ">")(2) as Issue
From [HD_Data]
0
 
KjBleauAuthor Commented:
Hmm, I get "Invalid use of '.','!', or'()'. in query expression 'split ...
I'm gonna keep toying with SPLIT though
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Pete,
FWIW,
For me, ...Your code works fine as a function(s):

Public Function SplitField1(Category As Variant) As Variant
    SplitField1 = Split([Category], ">")(0)
End Function
Public Function SplitField2(Category As Variant) As Variant
    SplitField2 = Split([Category] & ">", ">")(1)
End Function
Public Function SplitField3(Category As Variant) As Variant
    SplitField3 = Split([Category] & ">", ">")(2)
End Function

...Then in a query do something like this:
SELECT YourTable.ID, YourTable.Category, SplitField1([Category]) AS AssignedToGroup, SplitField2([Category]) AS Function, SplitField3([Category]) AS Issue
FROM YourTable;

Jeff
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
KjBleauAuthor Commented:
Thanks Jeff, I was kinda figuring it was a function that was required, but I'm not up on declaring functions in queries.
Can you simply add the block of text from "Public .." to ".. "End Function" somewhere in the SQL statement?
I am working on nested if statements (e.g.: Assigned to: IIf(InStr(1,[Category (class)],">")>0,Left([Category (class)],InStr(1,[Category (class)],">")-1)) but my head is spinning.
0
 
KjBleauAuthor Commented:
I solved it with IIF - thanks for the tips and the lead on functions, it appears that they are much more robust in that there were only a (relatively) few lines of code compared to this;
Assigned to: IIf((InStr(1,[Category (class)],">")=0),[Category (class)],IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))=InStr(1,[Category (class)],">"),Left([Category (class)],InStr(1,[Category (class)],">")-1),IIf(InStr(1,[Category (class)],">")=0,[Category (class)],IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))>InStr(1,[Category (class)],">"),Left([Category (class)],InStr(1,[Category (class)],">")-1),"false"))))
Function: IIf((InStr(1,[Category (class)],">")=0),"",IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))=InStr(1,[Category (class)],">"),"",IIf(InStr(1,[Category (class)],">")=0,[Category (class)],IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))>InStr(1,[Category (class)],">"),Mid$([Category (class)],InStr(1,[Category (class)],">")+2,(InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">")-InStr(1,[Category (class)],">")-2)),"false"))))
Issue: IIf((InStr(1,[Category (class)],">")=0),"",IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))=InStr(1,[Category (class)],">"),"",IIf(InStr(1,[Category (class)],">")=0,[Category (class)],IIf((InStr(1,[Category (class)],">")+InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">"))>InStr(1,[Category (class)],">"),Right$([Category (class)],Len([Category (class)])-InStr(InStr(1,[Category (class)],">")+1,[Category (class)],">")-1),"false"))))
0
 
KjBleauAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for KjBleau's comment #a38296038

for the following reason:

it was my own solution, and I didn't much like it. Brute force.
0
 
KjBleauAuthor Commented:
ok, my brute force solution was like the cologne, it stunk .. I'm trying to create a function now
0
 
KjBleauAuthor Commented:
Wow, amazing what the nakit light of day will do for your head.
I did learn to create a function (turns out that, that is the simple part ) but I am getting an error
" Run-time error '9': subscript out of range"
due (I belive) to some of the values in the [Category (class)] field NOT having a bracket.
in fact, there are a few that are NULL value

Looking (again) at the "split" function to see if I can figure it out, but any help from the 2 orginal guru's would be appreciated.
0
 
Jeffrey CoachmanMIS LiasonCommented:
FWIW, I am sure Pete could have gotteon his solution working for you.
0
 
KjBleauAuthor Commented:
Thanks <all> I am happy to point the points to Pete per Jeff's request.
I have temporarily negated the blanks and the fields with no bracket in the text by eliminating those records from the query <WHERE (((InStr(1,[Category (class)],">"))>"0"))> but that is only stop gap since the records are there and (from the perspective of the data) valid.
Any more great hints .. did I say thanks for the function?

Ken
0
 
Jeffrey CoachmanMIS LiasonCommented:
Ken, I am not sure what happened here....
The function I posted (That is based 100% on Pete's post) worked fine for me.
See the attached sample

So test this sample DB.
If it works, ...and you can get it working in your DB, then please award the points to Pete.

;-)

JeffCoachman
Database54.mdb
0
 
KjBleauAuthor Commented:
I didn't mean to award any points. The function worked with the data provided in the sample. Unfortunately, the sample didn't provide the two other types: blank and values without a bracket. When it comes across those, it fails.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then how about posting some actual data... in a sample DB?
0
 
KjBleauAuthor Commented:
Columns A -> J is what comes in CSV
HD-Data.xlsx
0
 
Jeffrey CoachmanMIS LiasonCommented:
<The function worked with the data provided in the sample. Unfortunately, the sample didn't provide the two other types: blank and values without a bracket. When it comes across those, it fails. >
You will have to post a clear example of what this means.

I tried the function on the data in the spreadsheet and nothing "Failed"
See attached file, qryHD_Data
Database54.mdb
0
 
KjBleauAuthor Commented:
was missing "option explicit" in my moduel .. not sure if that was the issue, but added it and it works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.