Numerical Sort Order

[Ice Rhino]

I have a database that contain a column that has box numbers in it

e.g. 1 PM, 2 PM, 3 PM through to 20 PM and above.

I am trying to find a way to show what the highest box number is that is within the column. However when I sort on Ascending or dexc, it is doing

1 PM, 10 PM, 11 PM and so on through to 2 PM, 20 PM etc etc.

How can I get the query to just show me the highest number? I tried adding totals and selecting MAX but that displayed 8 PM, Last showed me 12 PM.

I just want it to show me the highest box number that exists in the table.

Anybody?

 

[ColinEssex]

Its because your "numbers" are in fact text, because you have alpha characters in it.

Split the field and your numbers will work correctly.

Col

 

[Jon K]

The column can be sorted by Val([FieldName]).

To get the highest number, you can use the expression Max(Val([FieldName])) e.g.

SELECT Max(Val([FieldName])) AS HighestNumber
FROM [TableName];
.

 

[Ice Rhino]

I shall give that a go right now

Thank you for the very prompt reply

Regards

 

[Ice Rhino]

Worked like a charm

My sincere thanks to you

Regards