[AccessD] Parsing Variable Length Text String with VBA - Somebody didn't understand Normalization

I have worked with something like this before.
 
Now for what may seem like a silly question...in the text string...is it only 1 record(do all of the fields belong, 1 to 1 with the record they are attached)?  The data I was dealing with had a text field, representing multiple records as a subset of the record it belonged to.
 
Second question...not so silly...you stated 10 to 30 pieces of data...are there only 30...do you have a unique list of possibilities...and are they ALWAYS formatted 
the way you show below: "header_name ["  ?
 
If the first question is 'yes, only 1 record'...and the second is 'yes'...then I would build a list of of the header_names, and loop through the way you described below...for each header find position of "header[" and pull all until next "]".
 
If the answer to the first is 'No'...we will need more to drink and regroup.
 
Hope that helps.
 
mark
 
 
 
 
 

> Date: Wed, 7 Mar 2012 09:13:49 -0600
> From: BradM at blackforestltd.com
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Parsing Variable Length Text String with VBA - Somebody didn't understand Normalization
> 
> All,
> 
> We are using Access 2007 to pull data from a purchased package SQL
> Server database. 
> 
> One of the fields that we are working with is a long text string that
> actually contains multiple pieces of data that we would like to work
> with on an individual level.
> 
> Here is an example of how the pieces of data are found in the text
> string.
> 
> State [Florida] Country [USA] Color [Green] Division [Southeast] ...
> etc.
> 
> There can be from 10 to 30 individual pieces of data contained in the
> text string.
> 
> Ideally each of these pieces of data would be individual fields, but we
> cannot change how the data is stored in the purchased package.
> 
> We currently use Access 2007 to present this data to our users. This
> works nicely for the most part. 
> 
> Recently one of our managers has requested that we change the color to
> red for all of the pieces of data that are contained in the brackets.
> In other words, if we have the string 
> 
> State [Florida] Country [USA] Color [Green] Division [Southeast]
> 
> the words Florida, USA, Green, and Southeast would be shown in red on
> Access reports.
> 
> 
> 
> What further complicates all of this is the fact that there is a
> variable number of pieces of data. In this example, there are 4 pieces
> of data, but there can be up to 30 or maybe even more in the future.
> 
> We have done some experimenting with parsing the big text field and
> creating small fields by looking for the brackets and the data between
> the brackets - [Florida] for example . This will probably work, but
> before we go further with this approach I wanted to see if anyone else
> has ever run into something like this.
> 
> Thanks for your help,
> 
> Brad 
> 
> PS. The data cannot be stored in Rich Text Format - it currently can
> only be stored as simple text. 
> 
> PSS. I have an extra copy of a book on database normalization that I
> may send to the software vendor :-) 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com