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

Heenan, Lambert Lambert.Heenan at chartisinsurance.com
Wed Mar 7 09:37:30 CST 2012


Brad,

I would start by using the Split function to parse the data. For example...

? split("State [Florida] Country [USA] Color [Green] Division [Southeast]","]")(0)

Give the output...
State [Florida

In the above line, Split() has used the right bracket as its delimiter and thus returned a variant array of strings, the zero element of which is "[Florida": i.e. the delimiter is stripped off.

So your reports could parse the array in a loop and to whatever is necessary...

Dim vArr as Variant
Dim n As Long
Dim str as String 
	vArr = split("State [Florida] Country [USA] Color [Green] Division [Southeast]","]")
	For n = lBound(vArr) to uBound(vArr)
		' do something with element n of the array
		str = vArr(n) & "]" ' replace the missing bracket
		... Do other stuff
	Next n

Might get you started. :-)

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks
Sent: Wednesday, March 07, 2012 10:14 AM
To: Access Developers discussion and problem solving
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



More information about the AccessD mailing list