Brad Marks
BradM at blackforestltd.com
Wed Mar 7 14:51:25 CST 2012
Mark, 1. Yes, all of the pieces of data in the text string are all in one string in one record. 2. The list of pieces of data in the text string is finite. There will never be more than a specific number of them. We should be able to count on consistent "eye-catchers" (tokens) such as the headers and the brackets. I believe that with some work, we can break the big text field down into 30 little fields. I was curious if there was some sort of slick trick to do this. Thanks, Brad PS. Your suggestion to "drink and regroup" doesn't sound like a bad idea :-) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte Sent: Wednesday, March 07, 2012 2:29 PM To: accessd at databaseadvisors.com Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.