parse a field in a query

[auerdl]

I am using Access97.


The field in the query looks like...

John|A|Doe|30Mar2003|G1

I need to break this down into seperate fields...

Name Date Dept
John A Doe 30Mar2003 G1

What is the best way to parse this type of data?

Thanks in advance

 

[GoDawgs]

If the fields are fixed length then you can use the Left, Right, and Mid functions to split the data out...otherwise you're stuck with running through code and dividing it up (check out the InStr function, you're gonna need that). Hope that helps.

Kevin

 

[MichaelRed]

If the pipe delimiter is actually part of the field, you can use the "SPLIT" function (Ms. A. 2K & later). Otherwise search these fora for "basSplit" and use it instead. Generally, the parseing of name fields is hazzardous, so you should check that each record includes the same number of seperators, and -if not- the issue becones more complicated.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GoDawgs]

Where did this data come from? Any chance you can import it again (not read it from a table) and dump it to a table right away using some import specs to divide up the data?

 

[auerdl]

I need to have access divide it for me.
The database is for someone else, and they cannot split the data themselves.

I need a fomat to parse where it finds a |(pipe).

 

[Clandon]

Try importing the table into another access database using a spec with the |(pipe), creating a table. You can then import/export the new table into your original database.

Just a suggestion.

 

[auerdl]

MichaelRed,

Can you give me more information on how to use the "SPLIT" function in Access 2000?

Maybe that will work for me but I cannot find any "how to's" on this function.

thanks

 

[Clandon]

I've got the first 2 for you, the third will be a continuuance of the InStr commands looking for the 3rd |.

Field1: IIf(InStr([Test],"|&quot,Left([Test],InStr([Test],"|&quot-1))


Field2: Mid([Test],InStr(1,[Test],"|&quot+1,InStr(InStr(1,[Test],"|&quot+1,[Test],"|&quot-InStr(1,[Test],"|&quot-1)

The third gets tricky with the continuation of the InStr. Maybe someone knows something else.

Field4:Trim(Mid(Right([Test],12),1,9))

Field5:Right([Test],2)

Then you can join the first three fields back together for the full name.

Hope these help.

 

[MichaelRed]

Teh Ubiquitous {F1} (aka HELP) has the info. The only 'tricky' part is that the variable to which the return is assigned must either already be an array (of string) or a variant (to which an array may be assigned).

e.g.

[tab]Dim MyAry as Variant
[tab]Dim MyStr as String
[tab]MyStr = "The quick brown dog jumped over the lazy red Fox."

[tab]MyStrAry = Split(MyString, " &quot

Should return:

The
quick
brown
dog
jumped
over
the
lazy
red
dog.

as the elements of MyAry








MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Clandon]

Micheal:

Thanks for the info above. I could use that on something I've been working on for a while.

Field3: Mid([Test],InStr(1,[Test],"|&quot+3,InStr(InStr(1,[Test],"|&quot+3,[Test],"|&quot-InStr(1,[Test],"|&quot-3)