BREAKING APART A BIG FIELD 1

[Raptor136]

Hi All,
Not sure if this posted the first time. If it did then I apologize in advance. -- Rap

How do you break apart a big field. I have a text field that is 133 characters wide. What I'd like to do is run a query or code to take the contents of 1-10 and put them into another field in a different table. Then repeat with 11-25, etc. All help appreciated. Thanks.

Rap

 

[Steve101]

Use the mid function from within the query; for example:

SELECT mid(YourFld,01,10) AS fld0110,
mid(YourFld,11,10) AS fld1120,
mid(YourFld,21,5) AS fld2125,
mid(YourFld,26,9) AS fld2634,
.....
FROM tblYourTable
....

Change the mid starting position and width parameters and alias names as per your requirement.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Raptor136]

Steve, Big help. I am glad I do this as a hobby cuz there is a lot to learn. Need to bone up on SQL, but I follow the logic. Once I chance to try it I will get back with you. Thanks again -- Rap

 

[Raptor136]

Steve, you are the man. I took your stuff, modded it and added some MakeTable stuff and now I understand just how much I don't know yet. Awesome. Thanks much. -- Rap

 

[Steve101]

Hang in there Rap. Trouble is, the more you know, the more you realise just how little you know. Life's like that.
Good luck, Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

While the soloution answers the question -as posted, there are additional considerations which may be important. First, the simplistic "ten chars" per lump is generally going to just break up the existing information into an un-readable mess. Little niceties like word breaks or delimiters would (perhaps) help in the 'readability' issue. 170 characters in a field implies that this is a comment type of entry (not used for relationships, joins ...) so indexing would appear to not be a consideration (at least at first glance), but the breakup is not uniform, implying some (known) structure within the field - which then suggests the field contents may be parsed to provide information which might then be used in relatinships. Breaking up the information in this case would need to include some checks on the sub-fields' content to assure that the structure was preserved.

An additional issue here is simply the number of fields within the table (and subsquent queries). Breaking up hte 170 characters in a relatively large number of fields (15:20?) may easily push Ms. A. (and many other relational dbs) past their limit (for Ms. A, 255) of the number of fields within a recordset deffinition. Within these forums, there have been numerous threads boasting of the fact that Ms. A. does enforce the limitation. Many of these also point out that the limit is NOT based on the actual field count, but includes many other items, such as indicies and joins.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Raptor136]

Michael -- You got some valid points. My 10 char example was for simplicity sake. The report I am breaking is multiline tab delimited except that it isn't columnular <sp?>. The total fields counts up to over 100. This is my learning project. The thing I noticed in the test run I made from Steves advice was that the extraction of four characters in one instance of the Make Table qry still created a field of 255 characters. Thanks for the input, I will definetly keep it in mind to try to lower the field count. I wasn't aware of the limit (I thought 255 was the limit for tables, not the whole db.) -- Rap

 

[MichaelRed]

255 fields is a limit on the number of 'fields' in a RECORDSET, not a db. sorry if I mis-stated it. If your input is delimited, it is easier to use &quot;split&quot; (or basSplit which can be found in these forums) to seperate the fields.

You will get better and more complete advice on solving problems in these forums by accurately and completely stating the issue(s). Often, 'you' will simplify out important considerations, such as (in the above) the fact that the input is delimited.

Tab delimited files can usually be directly linked (or imported) to tables in Ms. A., obviating the need for parseing routines altogether. So an alternate soloution might be to export the field to a simple text file (with the delimiters intact) and then link/import the result as (Tab) delimited.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over