Text to Columns in access ???????

[unc2plo]

I need help duplicating "text to columns" in access.

I have a database field that is 6 characters log. I need the field broken down into 3 columns depending on where the hyphen is.

I tried using Left & Right, but the problem is that in some cases the entire 6 characters are used up, and not in others, but the extra character space is still there.

Example:
Shifts
12-2-1
3-1-2

Is there some sort of VBA code that can do the breakouts based on teh hyphen vs character count?


Thanks,
David

 

[xenou]

Run-time error '9'.
Subscript out of range.

And this result (I just copied first lines):

Text | My Left | MyMiddle | MyRight
A1; A1; #Error #Error
Q5 Q5 #Error #Error
Q5- Q5
X4-Usluga X4 Usluga Usluga
X7-Kosten 2015 X7 Kosten 2015 Kosten 2015

How can you both get an error (for every line??) and get results?
The results showing #Error are because the text does not contain the delimiter.

 

[megan80]

How can you both get an error (for every line??) and get results?
The results showing #Error are because the text does not contain the delimiter.

I think my answer got lost

So I get a Visual Basic Pop up window. Saying:

Run-time error '9'.
Subscript out of range.

I have four options

Continue (it's grey and not clickable)
End (it keeps popin up the same error until the results appear)
Debug (it leads to VB-Editor where the line SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1) is highlighted
Help (well...)

 

[megan80]

I think my answer got lost

So I get a Visual Basic Pop up window. Saying:

Run-time error '9'.
Subscript out of range.

I have four options

Continue (it's grey and not clickable)
End (it keeps popin up the same error until the results appear)
Debug (it leads to VB-Editor where the line SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1) is highlighted
Help (well...)

I decided to do it another way just checking if my codes appear on the Text-Column so I will get a yes Column for every Code which was introduced. Now I am not sure how to make it appear as a list....

Maybe someone gives me an Idea. It is like I have such a list:

Name Clothing
Person A 01-03-90
Person B 01
Person C 03-01
Person D
Person E 90

And I separated it like this

Name Clothing 01 03 90
Person A 01-03-90 01 03 90
Person B 01 01
Person C 03-01 01 03
Person D
Person E 90 90


Now I want to have a list like this (where 01=Hut; 03=Bluse; 90= Shoes)


Name Clothing
Person A Hut
Person A Bluse
Person A Shoes
Person B Hut
Person C Hut
Person C Bluse
Person D
Person E Shoes


I don't know if I am thinking to complicated....

 

[xenou]

Is your data in a text file? Changing Hyphens to spaces is about the same so nothing gained.

 

[megan80]

Is your data in a text file? Changing Hyphens to spaces is about the same so nothing gained.

No, it was actually separated in columns it just looked like spaces because I copied it in here and the format looks different.

 

[xenou]

What do you mean by columns? There are no columns in text files.