Ascending Order of Numbers 3

[mistereli]

Hi,

In my table have serial numbers from 1 through 99999 (example 34, 989, 12938 .....etc) but when I put them in ascending order it will put them in 12938, 34, 989 ... order. I think it sees the numbers as 12938, 34000, 98900 .... . I would like to place 000's in front of the 2/3/4 digit numbers so it reads them as their true numbers. The data type for the serial numbers is text (which I need to keep).

Does this make any sense? Can anyone help? Thanks in advance.

 

[1oldfoxman]

Text field, so the sort is happening as you would expect - textwise, 12938 comes before 34 (because "1" comes before "3&quot.

You need to either left pad the text with leading zeros as you suggest (PADL(fieldname,length,"0&quot or other function), or as an alternative index/sort them on a function that converts the text to numeric values e.g. VAL(fieldname), TO_NUM(fieldname).

If you'll indicate which database/language product you're using, someone will likely be able to give you the function you need to follow either of these paths.

HTH,
dennis

 

[mistereli]

I'm using MicroSoft Access....................

 

[mistereli]

1Oldfoxman,

Where would I put the (PADL(fieldname,length,"0&quot or other function)?

Thanks!

 

[Golom]

The function of PADL as 1OldFoxMan describes but you (at least I) can't use it in an SQL statement. I get an "Unknown function" error.

The work around is

Order By Right$(String$(Len,"0&quot & Table.Field, Len)

where Table.Field is the field name and
Len is the maximum length of the field.

 

[mistereli]

Where do I put your work around? Sorry, I'm new at this. Thanks!

 

[Golom]

That's OK. We were all "new at this" once ...

In the SQL for the retrieval as in

Select * From Table
Where blah blah blah
Order By Right$(String$(Len,"0&quot & Table.Field, Len)

and change 'Len' in both places where it appears to a numeric value that's at least as long as the longest text string in 'Table.Field'

 

[1oldfoxman]

Golom's correct. I was testing and came up with :
SELECT ......
.............
FROM TableName
ORDER BY RIGHT("00000000000000000000"+trim(fieldname),7);

His solution is better. (I'm not a terribly experienced Access programmer). PADL() is a FoxPro function and I mentioned it as an example without knowing what DBMS you are working with.

dennis

 

[sfvb]

Can't you just do:

ORDER BY CLng(Table.Field)

 

[Dimandja]

1oldfoxman,

So why not give Golom a star?

This forum (General Database Discussion) stands out as the least rewarding on this site.

Dimandja

 

[1oldfoxman]

Dimandja-
Here's a star for Golom (it wasn't my question to begin with, but hey, a good answer deserves a star .....) And one for Sfvb, whose input works too.

 

[Dimandja]

Nice gesture, 1oldfoxman!

Actually I meant to address mistereli and others who asked the questions.

Dimandja

 

[mistereli]

Golom, you helped me answer my question that I have been trying to figure out for some time. People like you keep me believing that there are still kind, helpful and patience people out there in the world. Thanks Again, MisterEli(Kim)