Reading comma seperated lists

[Gary Smith]

Hi, I've got a field that contains a list of rooms.

In most cases, this contains a single ID. However, under some
circumstances, the field may contain a list of two IDs which
are broken by a comma.

For example: BENL LT, RAT LT

At the moment, I've got the VBA bits working on the single
ID stuff, but, should it come across a list like the above,
I want it to parse both entries as two different entities.

How would I go about getting it to do this?

Hope that makes sense,

:-)

Gary

[Gary Smith]

Re: Reading comma seperated lists

On 2004-09-05, Gary Smith <shadyron@l33 t-d00d.co.uk> wrote:[color=blue]
> Hi, I've got a field that contains a list of rooms.
>
> In most cases, this contains a single ID. However, under some
> circumstances, the field may contain a list of two IDs which
> are broken by a comma.
>
> For example: BENL LT, RAT LT[/color]

Hmm, that was a really bad explanation of what I wanted to do. Sorry.

Let me clarify.

I've got a few tables, one of which is imported data. In this table,
is one field containing a list of rooms - usually, this contains a single
room. However, there's a few instances where this will contain two,
in the above form (ie "BENL LT, RAT LT"). There's a Form that goes
through this imported data and does Stuff[tm] to it with a script.

One of these things is it cross references these rooms against another
table in the database. However, when it comes across these rooms (ie a
comma seperated list, it doesn't recognise it, although the two on their
own are valid. How can I get the script to recognise these are two
distinct entities, rather than one?

Cheers,

Gary, hoping that makes more sense.

[rkc]

Re: Reading comma seperated lists


"Gary Smith" <shadyron@l33 t-d00d.co.uk> wrote in message
news:slrncjlso6 .sik.shadyron@p rofile.l33t-d00d.co.uk...[color=blue]
> On 2004-09-05, Gary Smith <shadyron@l33 t-d00d.co.uk> wrote:[color=green]
> > Hi, I've got a field that contains a list of rooms.
> >
> > In most cases, this contains a single ID. However, under some
> > circumstances, the field may contain a list of two IDs which
> > are broken by a comma.
> >
> > For example: BENL LT, RAT LT[/color]
>
> Hmm, that was a really bad explanation of what I wanted to do. Sorry.
>
> Let me clarify.
>
> I've got a few tables, one of which is imported data. In this table,
> is one field containing a list of rooms - usually, this contains a single
> room. However, there's a few instances where this will contain two,
> in the above form (ie "BENL LT, RAT LT"). There's a Form that goes
> through this imported data and does Stuff[tm] to it with a script.
>
> One of these things is it cross references these rooms against another
> table in the database. However, when it comes across these rooms (ie a
> comma seperated list, it doesn't recognise it, although the two on their
> own are valid. How can I get the script to recognise these are two
> distinct entities, rather than one?[/color]

Avoid the problem by splitting the records with multiple values when you
import the data.

or

Have the script check for an occurance of a comma, split the
field and process it as two records. Details of how to do that depend
greatly on the script that does the Stuff. Just splitting the field is
trivial
using Split() if you're post Access 97 or Instr() and Left(), Right(), Mid()
if your at Access 97.

[Gary Smith]

Re: Reading comma seperated lists

On 2004-09-05, rkc <rkc@yabba.dabb a.do.rochester. rr.bomb> wrote:[color=blue]
> Have the script check for an occurance of a comma, split the
> field and process it as two records. Details of how to do that depend
> greatly on the script that does the Stuff. Just splitting the field is
> trivial
> using Split() if you're post Access 97 or Instr() and Left(), Right(), Mid()
> if your at Access 97.
>[/color]

I had a think about this on the train (I was away over the
weekend) and ended up doing voodoo with Left(Instr()) and Right(Instr() in the
end, and stripping out any errant spaces that might've crept in and it seems
to have done the trick.

Cheers :-)

Gary