Split / Parse a string into substrings on new rows

[Splatgore]

Hi there,

First time posting (technically second time but I lost my first post ... grr!) so be gentle.

I have a table in Access with 2 fields; DelID (text) and AssetDesc (memo).

DelID is a unique asset ID and AssetDesc is a location string which can be up to 450 chars in length.

What I need to do is split AssetDesc up into substrings of no more than 70chars (or last full word before 70chars) and put each substring into a new row along with the original asset ID in the field next to it.

For example, I may have :

DelID | AssetDesc
ABC123 | This is a bloody long string with no delimiters which goes on and on and on, and so forth.

What I would want the output to be is :

DelID | AssetDesc
ABC123 | This is a bloody long string with no delimiters which goes on and on
ABC123 | and on, and so forth.

I expect that this will require some VBA trickery to solve but I have no idea how to go about it. I've never used VBA in Access so I am not too sure how to utilise it but I program in Excel quite frequently so I know the language quite well.

If anyone has any ideas I would be extremely grateful, and some tips also on how to use VBA in Access properly would result in double extreme gratefulness!

Kind regards,

Splatgore

 

[Galaxiom]

You will also want another field to indicate the order of the substrings in the new table. This table could use a composite index/key on DelID and the SubstringOrder field.

Access VBA and Excel VBA are very similar.

Use a loop that runs until the length of the working string is less than 70.
Use InStrRev to find the last space before the 70 character limit.
(You will also need to trap the possibility that no spaces remain in the string.)

Then the Left function get the string up to that point and the Mid function to write the rest of the string for the next time through the loop.

Increment a counter to maintian the SubstringOrder value.

Create a dynamic or paramaterised query to insert the values in the new table.

Don't forget to write the last substring after the string length has gotten below 70.

 

[Splatgore]

Thanks for the tip. I'll play around with your suggestion but I am sure I will be back with more questions.

 

[dwl_99]

Hi Splatgore and Galaxiom.
I have the exact same issue as Splatgore, but have no knowledge (yet) of VBA. I am using Excel to manage this data. Would you share the vba script and/or formulas used in excel to do this parsing?

thanks for your help!
DwL_99