By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Reading comma seperated lists

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 2004-09-05, Gary Smith <sh******@l33t-d00d.co.uk> wrote:
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


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.
Nov 13 '05 #2

P: n/a
rkc

"Gary Smith" <sh******@l33t-d00d.co.uk> wrote in message
news:sl*********************@profile.l33t-d00d.co.uk...
On 2004-09-05, Gary Smith <sh******@l33t-d00d.co.uk> wrote:
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


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?


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.

Nov 13 '05 #3

P: n/a
On 2004-09-05, rkc <rk*@yabba.dabba.do.rochester.rr.bomb> wrote:
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.


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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.