Split out info from a string

[Leesha]

Hi,

Well this one is sooooooooooo beyond anything I've learned here or a Woody's. I have a database that a string in a query that contains up to 1448 charcters that are dervied from different data entry tables. There are no commas or separators at all. If there was no date, those spaces are blank. I need to be able to break this string apart based on the length and the start and end points. There are 375 different items in the string.

For example, [record_id] has a legnth of 2, start point of 1 and end point of 2. [record type code] has a length of 2, start point of 3 and an end point of 4. So in the following example (I've only enclosed part of the string)

B1 2010051900A1111169C-07155

B1 would equal the [record_id] and there would be no value for the [record type code] as there is no data between teh start and end points. 20100413 would be the [assessment lock date] as it has a length of 8, a start at position 5 and end at 12.

I'm including a table showing the definitions. I didn't inlude a sample of the data as contains patient information and there was no way to change it all and be sure that nothing confidential was given away.

Thanks,
Leesha

[HansV]

What do you want the end result to be? An Access table can have a maximum of 255 fields, so it will not be possible to fit the 375 pieces as separate fields in one table.
If you need all 375 pieces, you could create 2 tables with each about 190 fields, with a one-to-one relationship.
Or if you need only part of the data, you might be able to fit them into a single table.

[Leesha]

My ultimate goal is to creat a query that breaks down the fields according to descriptions in the table I provided. I didn't even think about the 255 field limit as I was so brain dead at trying to find a way to do this. Two separate queried is fine. I need to be able to break apart of of patients information in order to quality reviews. I won't actually be using all of the fields, but since I never know what people will ask to have analyzed next I figured it would be best to break it all apart initially and then go from there. You mention tables vs queries. I'm curious as to what you think would be the best way to accomplish this.

Leesha

[HansV]

Is the table that you mention actually an imported text file?

[Leesha]

No, its actually a field in a table that contains other patient related information. This particual field contains the string of data that is compiled to be transmitted to the federal gov't for billing etc. The string itself is compiled by our software company and I have access to it to use however don't actually have access to all of the fields that that data in the string comes from. This is why I need break apart the string.

Leesha

[HansV]

The best solution would be to demand that your software company provides the data in separate fields instead of in one humongous string. They will undoubtedly protest that that is not possible, but that's complete nonsense. They have the data in a sensible format, and should be able to give it to you in a sensible format.

[Leesha]

I already spoke with them and this isn't going to happen. Is there no way to do this?

[HansV]

Why isn't it going to happen? They HAVE the data split into separate fields. Don't accept No as an answer!

[Leesha]

From what I understand the data is being gathered from html data vs stored in tables. Also, even if I could get this from them it certainly would't be in the time frame I need. We work very closely together and they are really great to work with. It is rare that they deny a request so when they do I do respect it. I'm not saying down the road they wouldn't be able to get this to me but unfortunately I need it now. I don't mind doing the work if I can "see" what the code in the queries would look like. Is it even possible?

Leesha

[HansV]

The attached database how you can use queries to split the data.
I have created a small table tblData to represent your data table with the long string (a memo field).
The query qryData splits the long string, with a separate record for each part.
The crosstab queries qryPart1 and qryPart2 display the fields next to each other.

OASIS200R3.zip

(I still think their argument is nonsense, by the way. They're just being stubborn and lazy.
Sorry about this rant, this is something I've seen far too often in my own work, it gets me really angry.)

[Leesha]

OMG what a work of art! I had envisioned hours and even days trying to replicate code over and over and then test to be sure it worked.

No problem on the ranting. I totally get it. I will say though, that even our IT engineer says that this is the one company he works with that when they say its the hardware its the hardware vs being a software glitch and passing the buck.

Thanks so much. You really are a wizard!

Leesha