Sorting table problem

[JeroenMioch]

Hi Peeps,

I have a rather strange problem.

A field (employee number)in my database had leading zero's e.g. 3717 was 003717.
For some reason i had to turn this around so i made a query to delete all leading zero's in my database.


This went well except for the numbers that had a letter in it. But thats easy correctable and not the issue.

After the query had ran i tried sorting the field and it seems to be aligning not propperly, like this :

12
1200
123

in stead of

12
123
1200

Is there a way to correct this ? The field is TEXT (as it was before i ran the query)
I would make it Numbers but i cant, the eployee numbers also have letters in them.

Help me please !

[June7]

Actually, nothing strange at all about this. Because the field is text the values are just series of characters, not a number value. The sort uses alpha rules, one character at a time left to right. Zero comes before 3.

You need the leading zeros as placeholders.

00012
00123
01200

This is why identifiers (like ZIP codes and SSN) should be fixed length, including leading placeholder zeros, and normally stored in text fields.

If the characters were all digits, could calculate a field to use for the sort:

Format([fieldname],"00000")

However, that won't work if alpha character is included.

Where are the alpha characters - at the right end? How many? Always the same number of alpha characters?

[JeroenMioch]

Right, that makes sense (unfortunately).
I know it's good practice to add leading zeros and to store as text. Thats why i constructed my db that way in the first place.
The only problem is that in the Human resource management system the employee numbers don't have the zeros and do have alpha characters.
If in the HR system someone has employee number T1717 i have to make it 000T1717 or T0001717. (the T is for Temporary btw) For the people that input the data into my system this is a problem because they dont only have to input it that way (which can be automated, i know) but they also have to search records that way.
So, i decided its best to input the characters exactly the way the HR system has them.

I think ill just leave it like this, they really dont have to look at the table anyway and if they ever do, ill just make a report wich can be sorted as it was a numeric field.
Thanks for the response June, Much appreciated