Change report sort order

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

Guest

How can I make my access report display the following numbers (123-1, 123-10, 123-2, 123-3 as produced by the query) display as (123-1, 123-2, 123-3, 123-10)? I have tried the suggestion of: ORDER BY Val([MyTextField]), but that did not correct the problem. Thanks
 
How can I make my access report display the following numbers (123-1, 123-10, 123-2, 123-3 as produced by the query) display as (123-1, 123-2, 123-3, 123-10)? I have tried the suggestion of: ORDER BY Val([MyTextField]), but that did not correct the problem. Thanks.
Click to expand...

Is the field always in the format of
Number dash number?
Add a new column to the query.

Sorting:Left([FieldName],InStr([FieldName],"-")-1) &
Mid([FieldName],InStr([FieldName],"-")+1)

In the Report's Sorting and Grouping dialog, write
=Val([Sorting])
Drag this up to the top row (if that is where you want it).
 
Dear Ed:

The procedure would be to split this column in two, then sort by those
two parts.

Before tackling the specifics of this, I need to know:

- Does the portion left of the hyphen always have 3 digits? If not,
do you want to sort alphabetically (1, 10, 11, 12, 2, 20, 21, 3, . .
..) or numerically (1, 2, 3, 10, 11, 12, 20, 21, . . .)?

- Is there always exactly one hyphen in the value? If not, please
describe the variations that may be found and how you want to handle
them.

The problem is really in your question itself. 123-1 is NOT a number.
It could be considered as two numbers, 123 and 1, with a hyphen
between. It could be considered as an arithmetic problem, 123-1 =
122.

I'm not saying this just to be picky, but because this is the way the
computer sees it. It is, as you also say, a text field. It cannot be
both a text field and a number, even if the text field did contain
only pure numbers. Sorting numbers and sorting text is decidedly
different.

Anyway, I think I can understand what you probably mean, but need just
a bit more information to proceed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thank you, Fred! That looks like it should do it. E

----- fredg wrote: ----

On Thu, 11 Mar 2004 11:36:05 -0800, Ed Sch wrote
How can I make my access report display the following numbers (123-1, 123-10, 123-2, 123-3 as produced by the query) display as (123-1, 123-2, 123-3, 123-10)? I have tried the suggestion of: ORDER BY Val([MyTextField]), but that did not correct the problem. Thanks
Click to expand...

Is the field always in the format o
Number dash number
Add a new column to the query

Sorting:Left([FieldName],InStr([FieldName],"-")-1)
Mid([FieldName],InStr([FieldName],"-")+1

In the Report's Sorting and Grouping dialog, writ
=Val([Sorting]
Drag this up to the top row (if that is where you want it)
 
Hi Tom... Thanks for responding. In this instance, yes, the first three digits are always followed by a hyphen, followed by numbers. I also need a solution to deal with the following format (123 A-1, 123 A-2 etc). The hyphen was bad enough, but this group also has a space as well. Appreciate your help. E

----- Tom Ellison wrote: ----

Dear Ed

The procedure would be to split this column in two, then sort by thos
two parts

Before tackling the specifics of this, I need to know

- Does the portion left of the hyphen always have 3 digits? If not
do you want to sort alphabetically (1, 10, 11, 12, 2, 20, 21, 3, .
..) or numerically (1, 2, 3, 10, 11, 12, 20, 21, . . .)

- Is there always exactly one hyphen in the value? If not, pleas
describe the variations that may be found and how you want to handl
them

The problem is really in your question itself. 123-1 is NOT a number
It could be considered as two numbers, 123 and 1, with a hyphe
between. It could be considered as an arithmetic problem, 123-1
122

I'm not saying this just to be picky, but because this is the way th
computer sees it. It is, as you also say, a text field. It cannot b
both a text field and a number, even if the text field did contai
only pure numbers. Sorting numbers and sorting text is decidedl
different

Anyway, I think I can understand what you probably mean, but need jus
a bit more information to proceed

Tom Elliso
Microsoft Access MV
Ellison Enterprises - Your One Stop IT Expert


On Thu, 11 Mar 2004 11:36:05 -0800, Ed Sc
 
Back
Top