Parse comma delineated table field

[SurveyorinVA via AccessMonster.com]

Good morning,

I have a field in a table called "Roles". The "Roles" will contain a series
of numbers separated by a comma, such as 1,4,6,17,8,2,15.

What I want to do is to parse this field by the comma delineation and assign
it to a variable within VBA routine.

What would be the best practiced method to completing this? Would I use the
split() function or the Mid function, or is there another way?

Thank you in advance,
Chris

 

[Stefan Hoffmann]

hi Chris,

I have a field in a table called "Roles". The "Roles" will contain a series
of numbers separated by a comma, such as 1,4,6,17,8,2,15.

This is bad design, cause your field contains non-atomic data. This is a
violation of NF1.

What would be the best practiced method to completing this? Would I use the
split() function or the Mid function, or is there another way?

There are many ways. Split() is the fastest, in runtime and by code.

mfG
--> stefan <--

 

[Robert Morley]

Us database types sometimes forget that not everybody knows "the rules". If
"NF1" was Greek to you, it's short for "First Normal Form". You can read
about it here, amongst many other places:
http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

And while it's almost always best to follow these rules, there are times
when you have to break them for one reason or another. If having all the
data in a single field works best for what you're doing, don't follow the
rules just to arbitrarily follow the rules. As with so many other
things, it's always a judgement call.


Rob

 

[LarryP]

But now that you know more about first normal form, here's an actual answer
that might work:

--in VBA code, store your original string of comma-separated numbers in
a "working string" variable
--then in a VBA loop ("do this until the length of my working string is
zero"),
-- test whether the working string has reached zero length; if so, bail
out
-- if not, store the first number (note 1 below) into a second variable
-- do whatever you need to with this second variable, which will be the
first
number found in your working string on this pass thru the loop
-- reset your working string to "lop off" the number you just used (note
2 below)
-- repeat the loop

Note 1: Left(workingstring,Instr(workingstring,",") - 1) should give you the
first number in your working string, stopping when the first comma is
encountered

Note 2: workingstring = Mid(workingstring,Instr(workingstring,",") + 1)
should
truncate your working string by lopping off the first number and its
associated
comma

Note 3 about notes 1 and 2: If your final number ISN'T followed by a comma
it'll cause problems; I'd suggest solving that by tacking a final comma onto
your working string when you first build it

Good luck!