order by text value?

Results 1 to 3 of 3

Thread: order by text value?

  1. #1
    Join Date
    Dec 1969

    Default 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. #2
    peterjl@austec.net.au Guest

    Default 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. #3
    peterjl@austec.net.au Guest

    Default 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