Sort numbers stored as texts as numbers

[Guest]

I built a pivotchart form from a query that uses numbers stored as text (from
a linked table from an Excel spreadsheet). I would like to sort the numbers
stored as text as numbers (Excel gives you the option) i.e. 1, 2, 3... and
not 1, 10, 11, 2... so that in my X axis of my pivotchart they are in the
right order (they cannot be stored as number as one of the data is a letter).
I have tried creating a table numbering each department in the right order
and sorting my query through this field that I then uncheck so that it is not
visible and although it sorts right in the query itself, the order is still
wrong in the pivotchart.
I have also tried to change the order in the datasheet of the pivotchart (as
suggested by Access Help) and although it works, the order reverts to the
text sort when I reopen the chart.
Any help would be appreciated.
Sandra

 

[Steve Schapel]

Sandra,

The answer depends a lot on what exactly you mean by "they cannot be
stored as number as one of the data is a letter".

 

[Guest]

Try adding a field in your query just for sorting like this ---
Sort_Field: Format([YourField],"00000000")

SELECT Dee.xx, Format([xx],"00000") AS Expr1
FROM Dee
ORDER BY Format([xx],"00000");

This to this -----
xx Expr1
1 00001
10 00010
12 00012
20 00020
110 00110
a1 a1
a10 a10
a110 a110

 

[Pieter Wijnen]

You can do something like

SELECT ....,
IIf(IsNumeric(SortField),Format(SortField,'0000000000'),SortField) As Sort
FROM MyTable
ORDER BY Sort

HTH

Pieter