Split value

[CorbinMyMan]

I have a column in access that contains values that are seperated by a semi-colon ( and I want to split these values. For instance:

The column contains the following value:

"String1;String2;String3"

I want to split it at the semi-colon and place each value in its own row:

"String1"
"String2"
"String3"

Any help or advice would be greatly appreciated!

Thank you!

 

[abaldwin]

Normalize your data. This is a terrible way to store data and makes for very hard reporting, use, manipulation.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[CorbinMyMan]

Well its not anything I use for reporting, its data I imported, and then I need to split that column by the semi-colon, and grab all the distinct values. I wont' even be putting them back into access, I just need a list of them.

 

[flyover789]

as far as I see this can only be done with VBA:

Function testsplit()
Dim db As DAO.Database
Set db = Application.CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tbl_TestSplit")
Dim ary
ary = Split(rs.Fields(1).Value, ";")

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
Debug.Print ary(0)
Debug.Print ary(1)
Debug.Print ary(2)

rs.MoveNext
Loop

End Function

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[CorbinMyMan]

Ok how about this, would it be posssible to just grab all the characters to the left of the first Semicolon?

For example
"String1;String2;String3"

and extract just
"String1" because its characters are all to the left of the first semicolon?

Thanks!

 

[PHV]

Have a look at the InStr function:
NewField: Left([yourField],InStr([yourField],';')-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886