order by text value?

# Thread: order by text value?

1. Senior Member
Join Date
Dec 1969
Posts
613

## order by text value?

I have a list of employee numbers that I have stored as text. (they need to be text in case of future use of alpha numeric emp numbers.<BR><BR>Problem is that when I order by these employee numbers they use the text hieracrhchy mening that 5 comes after 40... <BR><BR>Any ideas on this should I convert in my sql call? Will this be a problem with alphanumeric emp idS?<BR><BR>Thanks,<BR><BR>Scott

2. peterjl@austec.net.au Guest

## RE: order by text value?

The simplest way to make sure that text numbers sort numerically is to left-pad them with zeros:<BR><BR>&#039;5&#039; becomes &#039;005&#039;<BR>&#039;40&#039; becomes &#039;040&#039;<BR><BR>This function would do that:<BR>&#060;%<BR>Function LeftPad(Number, Zeros)<BR>LeftPad = Right(String(Zeros,"0") & Number,Zeros)<BR>End Function<BR><BR>EmpID = LeftPad(EmpID,4) &#039; left pad the the id to make a four-character ID String.<BR>%&#062;

3. peterjl@austec.net.au Guest

## PS:

I mean to add that although my solution would fix your sorting problem, I don&#039;t see why you need to worry about "in case of future use of alpha numeric emp numbers". If that happens you would just convert the numeric field to text.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•