Sorting Delimited Text in a Text Box

[Tim Marshall]

Access 2003. The situation is this: I have a "criteria selection form"
in which there are a many different criteria, mostly displayed as combo
and text boxes in which a user can enter stuff, click an associated
button and that criteria becomes part of the where clause of a select
statement. The selected criteria is stored on a hidden form and when
the user is ready to run their report(s), a button action goes through
the hidden form and builds a select statement.

For example, say buildings has a partial combo box list which shows:

PK (hidden) Bldg Name
1 Arts Building
2 Biology Building
6 Chemistry Building
8 Student Building

A user want to look at records from Chemistry and Arts, so the hidden
form "SQL WHERE STAEMENT" field would store values like this when Arts
was selected:

BLDG_PK = 1

Then, altered to look like this when Chemistry is selected:

BLDG_PK IN (1, 6)

And so on as more buildings may or may not be selected.

That's all well and good. However, along with the "SQL WHERE STAEMENT"
field, there's also a "CRITERIA DESCRIPTION" field. This adds the
Building Names so that the user sees a criteria view somewhere that is
also reproduced as a report header which lists the buildings selected.

I'm using a line break, vbcrlf, as a delimiter here. So if the user
selected in the same order as in the example, the "CRITERIA DESCRIPTION"
field will look like this:

Arts Building
Chemistry Building

But what if the user selects Chemistry first, then arts? What is stored
in the "CRITERIA DESCRIPTION" field will be:

Chemistry Building
Arts Building

What I am trying to figure out is how to sort this, alphabetically.

Any ideas or gentle/violent prods? And yes, I know I have to take care
of possible duplicates which is easy enough to do... 8)

Thanks very much in advance.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[PC Datasheet]

You need to do your sorting as each selection is made. How about putting the
selections in a collection rather that your "CRITERIA DESCRIPTION" field and
sort as you add to the collection. That way you have a simple list. Then
when you need the list in the report header, you build your "CRITERIA
DESCRIPTION" string using a line break, vbcrlf, as a delimiter. You need a
sub procedure to do this which gets called after each selection. Obviously
it does not get called after the first selection. To sort, you take the
latest selection and walk the collection inserting the latest selection
immediately following the first item in the collection list that has a sort
value less that the latest selection. Inserting an item means assigning an
index number to the selection. Sounds complicated until you start working it
out!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.
"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:dj******** **@coranto.ucs. mun.ca...

Access 2003. The situation is this: I have a "criteria selection form"
in which there are a many different criteria, mostly displayed as combo
and text boxes in which a user can enter stuff, click an associated button
and that criteria becomes part of the where clause of a select statement.
The selected criteria is stored on a hidden form and when the user is
ready to run their report(s), a button action goes through the hidden form
and builds a select statement.

For example, say buildings has a partial combo box list which shows:

PK (hidden) Bldg Name
1 Arts Building
2 Biology Building
6 Chemistry Building
8 Student Building

A user want to look at records from Chemistry and Arts, so the hidden form
"SQL WHERE STAEMENT" field would store values like this when Arts was
selected:

BLDG_PK = 1

Then, altered to look like this when Chemistry is selected:

BLDG_PK IN (1, 6)

And so on as more buildings may or may not be selected.

That's all well and good. However, along with the "SQL WHERE STAEMENT"
field, there's also a "CRITERIA DESCRIPTION" field. This adds the
Building Names so that the user sees a criteria view somewhere that is
also reproduced as a report header which lists the buildings selected.

I'm using a line break, vbcrlf, as a delimiter here. So if the user
selected in the same order as in the example, the "CRITERIA DESCRIPTION"
field will look like this:

Arts Building
Chemistry Building

But what if the user selects Chemistry first, then arts? What is stored
in the "CRITERIA DESCRIPTION" field will be:

Chemistry Building
Arts Building

What I am trying to figure out is how to sort this, alphabetically.

Any ideas or gentle/violent prods? And yes, I know I have to take care of
possible duplicates which is easy enough to do... 8)

Thanks very much in advance.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Bruce Rusk]

I'm using a line break, vbcrlf, as a delimiter here. So if the user
selected in the same order as in the example, the "CRITERIA DESCRIPTION"
field will look like this:

Arts Building
Chemistry Building

But what if the user selects Chemistry first, then arts? What is stored
in the "CRITERIA DESCRIPTION" field will be:

Chemistry Building
Arts Building

What I am trying to figure out is how to sort this, alphabetically.

Maybe I'm misunderstandin g, but it sounds like this is a job for a list box.
Why not have a list box with a rowsource generated on the fly?

If at some point you need to read the text from the Criteria Description,
you could either

- create recordset from the same source as the list box's rowsource
or
- iterate through the rows of the list box and concatenate, if you want to
create a string

It just seems unnecessary to create a string with line breaks and worry
about its appearance, etc., when you can just feed some SQL to a control and
let Access take over.

HTH,

Bruce