Displaying numbers in normal sequence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list box on a form that displays a field from the table. The data in
table is OF#1, OF#2,....OF#10, OF#11...

When the list displays, it displays in this order: OF#1,...OF#10,
OF#11,..OF#2, OF#3..etc.

I know I can renumber the single digit numbers with a zero in front of the
digit and they will display in regular sequence but it there another way I
can get the list to display normally (1,2,3,....10,11,12...etc)

Thank you.
 
I have a list box on a form that displays a field from the table. The data in
table is OF#1, OF#2,....OF#10, OF#11...

When the list displays, it displays in this order: OF#1,...OF#10,
OF#11,..OF#2, OF#3..etc.

I know I can renumber the single digit numbers with a zero in front of the
digit and they will display in regular sequence but it there another way I
can get the list to display normally (1,2,3,....10,11,12...etc)

Thank you.
Click to expand...

The List box is correctly sorting the field because it is a Text
field.
Create a query to be used as the List Box row source.
Add a new column:
SortThis:Mid([FieldName],4)
Sort on this column

The above assumes the number always starts at the 4th character.
 
As Fred wrote assuming that the data in table always start with OF# the
reason why it sorted that way is that the field is text type, so after we
will remove the four leading letters as Fred saggested, will have to convert
it to number

Select MyField From MyTable Order By clng(Mid([FieldName],4))
or
Select MyField From MyTable Order By cdbl(Mid([FieldName],4))
depend on the value you have.
 
Back
Top