?
Solved

converting text field to 3 columns

Posted on 2012-08-14
17
Medium Priority
?
387 Views
Last Modified: 2012-09-04
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
0
Comment
Question by:KjBleau
  • 10
  • 5
16 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38294212
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
 

Author Comment

by:KjBleau
ID: 38294783
Hmm, I get "Invalid use of '.','!', or'()'. in query expression 'split ...
I'm gonna keep toying with SPLIT though
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38294912
<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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:KjBleau
ID: 38294953
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
 

Author Comment

by:KjBleau
ID: 38296038
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
 

Author Comment

by:KjBleau
ID: 38298341
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
 

Author Comment

by:KjBleau
ID: 38296896
ok, my brute force solution was like the cologne, it stunk .. I'm trying to create a function now
0
 

Author Comment

by:KjBleau
ID: 38296980
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38297121
FWIW, I am sure Pete could have gotteon his solution working for you.
0
 

Author Comment

by:KjBleau
ID: 38298388
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38298540
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
 

Author Comment

by:KjBleau
ID: 38306819
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38318693
Then how about posting some actual data... in a sample DB?
0
 

Author Comment

by:KjBleau
ID: 38329333
Columns A -> J is what comes in CSV
HD-Data.xlsx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38330376
<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
 

Author Comment

by:KjBleau
ID: 38363644
was missing "option explicit" in my moduel .. not sure if that was the issue, but added it and it works!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question