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

 

[boblarson]

Just use

For the first one:

Left([FieldName], Instr(1, [FieldName], "-") -1)


and for the second:

Mid([FieldNameHere], InStr(1, [FieldNameHere], "-") + 1, Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))

And for the last

Right([FieldNameHere], Len([FieldNameHere]) - InStrRev([FieldNameHere], "-"))

 

[boblarson]

Sorry, the MID isn't working for me. So I'm going to do it another way.

 

[boblarson]

Put this function in a standard module and then call it as you wish:

Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
    SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function

In the query you can pass the value like:

SELECT SplitField([YourFieldNameHere],"-",1) AS TheLeft, SplitField([YourFieldNameHere],"-",2) AS TheMiddle, SplitField([YourFieldNameHere],"-",3) AS TheRight
FROM Table4;

 

[unc2plo]

Bob,
Thanks for the quick response.

I am afraid I am not following you entirely though.

I added the VBA to a new module, but I am not sure how to add it to the query.

This is a field in a larger table, and I want to still tie it to other data.

Thanks,
David

 

[boblarson]

Just create new fields in your query in the QBE grid (the other example I posted used the actual SQL instead).

So, in the field area you type this for the left one

MyLeft:SplitField([YourFieldNameHere],"-",1)

and the middle

MyMiddle:SplitField([YourFieldNameHere],"-",2)

and the right one

MyRight:SplitField([YourFieldNameHere],"-",2)

and as long as you named the module something other than SplitField (you don't name the module the same name as a procedure inside of it, to make it less difficult to reference).

And that is it. Just remember to replace YourFieldNameHere with the actual name of your field which has the 12-2-1 in it.

 

[muhammed82]

please can u help me to seperate the below i need only the text and plaese be informed that the data type is text

110502-مصاريف اشتراكات كهرباء و ماء و هاتف
12-الموجودات المتداولة
1201-الزبائن
i need to seperate the text from column in one criteria

 

[megan80]

Just create new fields in your query in the QBE grid (the other example I posted used the actual SQL instead).

So, in the field area you type this for the left one

MyLeft:SplitField([YourFieldNameHere],"-",1)

and the middle

MyMiddle:SplitField([YourFieldNameHere],"-",2)

and the right one

MyRight:SplitField([YourFieldNameHere],"-",2)

and as long as you named the module something other than SplitField (you don't name the module the same name as a procedure inside of it, to make it less difficult to reference).

And that is it. Just remember to replace YourFieldNameHere with the actual name of your field which has the 12-2-1 in it.

Hi there!
I have a similar Problem with a text separated with ";". I tried to use your Formula (Thanks for sharing!!) but the system is complaining:

"There was an error compiling this function
The Visual Basic module contains a syntax error.
Check the code, and then recompile it."

I didn't change anything yet (was going to change my ";" for "-" to check if it works).
So I just created the Module (Called it sth different) and paste the "Formulas" on my Query. Does somebody now what could be wrong?
Thanks a lot for your time!

 

[boblarson]

So I just created the Module (Called it sth different) and paste the "Formulas" on my Query!

1. I don't understand what you mean by "Called it sth different."
2. Pasted what in your query?

 

[megan80]

1. I don't understand what you mean by "Called it sth different."
2. Pasted what in your query?

Ok sorry I explain it slowly. I changed my MS Office Access to English (I was working in German, don't know if that's the reason for the other error I was getting...)

1. I created a Module with your Code:

Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function

I saved it and called it "M_Trennen"

2. I created a query (Q_Trennen) and (in Design view) added the field I want to separate [Text] + 3 further columns

Column1: [Text]
Column2: MyLeft:SplitField([Text];"-";1)
Column3: MyMiddle:SplitField([Text];"-";2)
Column4: MyRight: SplitField([Text];"-";2)

Note that I had to change "," with ";" because Access was complaining.

Now I get this error, which seems to appear for every line (63.000!!):

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

Do you know why I get the Run-Time error?
Can I easily replace the "-" for a ";"?
I could have up to 5 different codes separated with ";" can I do more Middleparts?
I am sorry for all the questions but I am trying for days to find a solution to that and I don't get it. I just did it with Excel until now but it's so annoying having to use Excel only for this.
Many thanks in advance!