converting text field to 3 columns

Hmm, I get "Invalid use of '.','!', or'()'. in query expression 'split ...
I'm gonna keep toying with SPLIT though

<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

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.

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"))))

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.

ok, my brute force solution was like the cologne, it stunk .. I'm trying to create a function now

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.

FWIW, I am sure Pete could have gotteon his solution working for you.

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

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

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.

Then how about posting some actual data... in a sample DB?

Columns A -> J is what comes in CSV
HD-Data.xlsx

<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

was missing "option explicit" in my moduel .. not sure if that was the issue, but added it and it works!