Parsing a Text Field (Access 97)

[DanAuber]

I have a text field that has values as follows (for example)

ABC\56GHHTAA\HGS 8988922222
DEFG\788788HAAAAK\IIIW JJJJJ

etc etc

I want to write an Access Query that takes this field and separates the data into 3 new fields thus:

ABC 56GHHTAA HGS 8988922222
DEFG 788788HAAAAK IIIW JJJJJ

(i.e. taking the \ as a column separator).

Does anyone know how to do this in an Access Query in Access 97 ?

thanks for any help on this....

Dan

Dan Auber

 

[hneal98]

What version of Access are you using?

 

[DanAuber]

errr...Access 97 ?

Dan Auber

 

[lespaul]

he said, 97!

in later versions you can use the Split function and I'm pretty sure in one of the Access FAQ areas, someone has written a function that simulates this in Access 97. You'd have to search the FAQs for it.

les

(if I find it I'll post a link)

 

[lespaul]

It's FAQ705-1823

les

 

[DanAuber]

Thanks for help so far - but still struggling a little bit (!).

If I attempt to use the SPLiT function I have now created in Access 97, all I get is a blank field.

Let us say, for example, I have Field1 in Table1 which is ABCDF\HJHJHJ\YUYUYYU

and I want to create a table Table2 that has ABCDF in field1, HJHJHJ in field2, YUYUYYU in field3. How exactly would I use this function. I tried using a query and the Expression builder - but I'm just getting blanks ??

thanks for any help

Dan Auber