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

Brad,

<<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.>>

  This is a viable approach depending on how the data is used and with
functions like Split() is probably easier to implement that you might
imagine.

  In fact routines to handle strings like this first appeared in the Access
Developers Handbook version 2.0, where they demonstrated storing multiple
values in a controls Tag property delimited by semi-colons.

  Many of us have used the same technique with the OpenArgs argument,
because you cannot pass multiple values in, just a single one.

  As long as your not going to work with the data relationally (you don't
want to query on it, sum it, etc), you'll be fine.  In fact I have one
client right now where I was forced to stuff data like this into a memo
field.  That ended up being in place for a number of years and it worked
without issue.  However we did nothing with the data other then store it and
then extract it for things such as printing labels.  Relationally it was
never worked with.

 But if you are going to work with it relationally, then you really need to
break out the data and normalize it.

Jim.

-----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 -
Somebodydidn'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