Sorting and Grouping option

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm sorting a field named [bank]. The data in that field is Commercial,
Corporate or Consumer. When I sort descending the order is Corporate,Consumer
and Commercial which is correct. I would like to sort Corporate, Commercial
and then Consumer. Is there a way? Thanks in advance.
 
Ideally, you should add a field to the bank that describes your sort order.
Otherwise, enter this expression in your sorting and grouping:
=Instr("Commercial Corporate Consumer",[Bank])
 
Or, change the field to a combo box (if it isn't already), and on the field
properties Row Source Type, select Value List. In the Row Source, type
Corporate;Commercial;Consumer
 
I tried your suggestion for sorting and it worked beautifully.
I used: InStr("Business Services Administration Consulting Services
Individual Services",[OffName])
Iv'e never used InStr and wanted to know "why" it worked. When I looked up
the explaination of the function I found:
Returns a Variant (Long) specifying the position of the first occurrence of
one string within another.

Syntax: InStr([start, ]string1, string2[, compare])

I still don't understand why this works for sorting. Any chance you can
tell me in clear, concise English the "why"?

Thanks!

Duane Hookom said:
Ideally, you should add a field to the bank that describes your sort order.
Otherwise, enter this expression in your sorting and grouping:
=Instr("Commercial Corporate Consumer",[Bank])

--
Duane Hookom
MS Access MVP


PJ said:
I'm sorting a field named [bank]. The data in that field is Commercial,
Corporate or Consumer. When I sort descending the order is
Corporate,Consumer
and Commercial which is correct. I would like to sort Corporate,
Commercial
and then Consumer. Is there a way? Thanks in advance.
Click to expand...
Click to expand...
 
The value for InStr will be 1 when OffName is Business, 10
for Services, 20 for admin, etc. so sorting those numbers
will produce the desired results.
--
Marsh
MVP [MS Access]

I tried your suggestion for sorting and it worked beautifully.
I used: InStr("Business Services Administration Consulting Services
Individual Services",[OffName])
Iv'e never used InStr and wanted to know "why" it worked. When I looked up
the explaination of the function I found:
Returns a Variant (Long) specifying the position of the first occurrence of
one string within another.

Syntax: InStr([start, ]string1, string2[, compare])

I still don't understand why this works for sorting. Any chance you can
tell me in clear, concise English the "why"?


Duane Hookom said:
Ideally, you should add a field to the bank that describes your sort order.
Otherwise, enter this expression in your sorting and grouping:
=Instr("Commercial Corporate Consumer",[Bank])

PJ said:
I'm sorting a field named [bank]. The data in that field is Commercial,
Corporate or Consumer. When I sort descending the order is
Corporate,Consumer
and Commercial which is correct. I would like to sort Corporate,
Commercial
and then Consumer. Is there a way? Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top