Listbox Leading-0 Sort (1 Viewer)

[NMeeker]

I have a Listbox "on a form" populated by several dynamic ranges pulling from several worksheets. I have already figured out how to take out all duplicates from the Listbox. I have also managed to try several different methods of sorting my information. However, I always seem to come up short of my goal.

It is a single column list.
The list has both numeric and Alpha-Numeric text entries.

Sorting by A-Z gives me number orders like this: 2, 3, 31, 32, 4
and sorting Numerically gives me even more issues.

So far, I have read several posts of small mentions of Leading-0 sorting, and I have seen one post that talks about "Sorting Complex Alpha-Numeric Strings." I would NOT like to copy the whole list back to a worksheet!

Can anyone help me come up with some code to sort a Listbox using Leading-0's? .... without changing the final text of the list... possibly a bubble sort or something?

 

[KenHigg]

So you want to numeric and still have leading zeros?

 

[NMeeker]

Thats right!! I think.... here is the only link i can find where someone actually talks about what I want to do, but I can't figure out how to incorporate it.

http://www.access-programmers.co.uk/forums/showthread.php?t=120275

 

[KenHigg]

I have not tried this but maybe you can base your list box rowsource on a query that has two colums, one as text with leading zeros and the other as a numeric data type. Then sort on the numeric but only show the text column...

Would that work?

 

[NMeeker]

I was kind of thinking along that line. But I am fairly new to this type of programming and I barely have any idea how to do that. Would anyone have any idea how to generate a second column of text with leading zeros, based on the first column? and after that... any idea how to show only the first column? or delete the second column after the sort has been done?

Due to the dynamic ranges the information is pulling from...
Every time a new entry is made in the database, The list will be getting bigger, and it will need to run the sort again....