Delimiters and Hierarchy

I have two questions.

1st: Can a secondary delimiter be added to delimit data segments with in the same data string? If so, how?

What I have:

ST*837*1234*BHT*0019*00*0123*2345678*1023*CH*REF

What I need:

ST*837*1234~BHT*0019*00*0123*2345678*1023*CH~REF



2nd: The basic format of the text file I am attempting to create in Access has the following dimensions:

Interchange (control header)
Detail (control header)
-DETAIL-
Detail (control footer)
Interchange (control footer)

Each section is assigned a reference # that not only references the section itself, but the hierarchy between the data segments (the hierarchy reference #s must be unique within the transaction set). Can this hierarchy be accomplished within a query or set of queries?

example:


IA*345*1234~ST*837*1234~BHT*0019*00*1234*2345678*1023*CH~
REF*88*046546105~HLA*1002*46189792*4681~HLB*1002*46415~

Reference (1234) doesn't pose a problem, because it is primary and is carried through the entire file. Reference (1002) however is contained in each detail segment looped within the file. For each detail segment Reference (1002) is incremented by 1. Each file will contain 20 to 150 detail segments.


I'm on a killer deadline, any help will be greatly appreciated!!

I think there can be only one delimiter. You will have to make the change to the string everytime you use it and change it back when you want to save it. Look to string minipulation functions in VBA for this.

The second question I will try to answer by saying the database can seperate the detailed sections from each other by the position in the string and the primary reference. If you are not useing the postion of the detailed segment in the string to find your one to many relationship the detail segment reference number is not unic and because of this you need the primary reference number to make the detail segment unic. You may consider combining them.

I see no reason that you may assign a detail segment reference number yourself, but then it falls on you to make sure that the number is unic with in the primary reference group. I don't think that access requires a perticular reference number just so it is unic. I do not know if you can use queries to access a system table. Make a copy of your file and try to alter the system table. If you have direct access to the text in the file use VBA it would be easier to make the changes. I hope this helps alittle.