text to column in Access with out having to export/import

[Guest]

I know there is a way (and for the life of me I can't remember how) to do a
text to column in Access.

Background...

I built a passthrough query that I fought with and finally got to work. I
don't want to change the query. At the end of the query it gives me a path to
the hierarchy in my org: SYS_CONNECT_BY_PATH(last_name, ',') "PATH"

This puts it into a memo field which is fine but now I would like to use a
query to make it a text to column for other reasons.

the variables in the string change or I would use a LTrim(Last_Name)1,5)

I thought I could tell it to look for the ',' but that's a lot beyound my
brain capacity at the moment.

any ideas?

thanks

 

[Douglas J. Steele]

The concept of "text to column" doesn't really have any place in a
relational database, so there isn't anything built into Access.

There's a Split function in VBA that can take a text value and split it into
various other text values based on whatever delimiter you choose (see
http://msdn.microsoft.com/library/d...html/fb2bbb28-85bc-42fc-85fb-ccc7da8abe8c.asp
for details) With that, you could conceivably write an Update query that
would take the first substring found and put it in field A of your table,
the second substring found and put it in field B of your table and so on,
but there's nothing that's equivalent to Excel's Text-To-Column capability.

On the other hand, it's not really clear to me what you're trying to do. If
all you need is to find where in a string a comma appears, use the InStr
function: InStr(MyString, ","). That will return 0 if there is no comma, or
the position in the string of the first comma found (there's also an
InStrRev that will give you the position in the string of the last comma
found). Once you know that, some combination of the Left, Right and Mid
functions will let you manipulate the string to only return a specific
substring.

 

[Guest]

That is perfect.

Thank you oh great Jedi Access Master.

 

[i_takeuti]

That is perfect.

Thank you oh great Jedi Access Master.