Sorting numbers in form

[oni1983]

I have a drop down list with numbers from 1 to 20 which I use in a form. The numbers when sorted appear like this:

13
14
15
1
2
3
4

I would like to change numbers 1 to 9 to 01, 02 etc so that numbers when sorted appear like this:

01
02
03

However i am afraid to edit the numbers in the drop down list as I'm not sure if the data will automatically refresh i.e. if a record was 1 and now in the list there is only 01, will the record automatically be associated with 01?

I hope I'm explaining my problem clearly!

 

[xenou]

Hi,
How do you populate this list in the drop down?

 

[welshgasman]

That sound like the numbers are actually text?
Though if that was the case it should be
1
13
14
15
2
3
4

 

[oni1983]

That's actually how they appear (I made a mistake in my post )

The list is a combo box created manually in the table i.e. not a different table with a relationship. I confirm that the list is in text format.

 

[xenou]

The list is a combo box created manually in the table i.e. not a different table with a relationship. I confirm that the list is in text format.

I'm not sure what you mean. Is the data in a table? if so you should change the table to have a number data type so it can sort properly. Or change the formatting to include leading zeros on the one digit numbers.

Your original question isn't clear either:

However i am afraid to edit the numbers in the drop down list as I'm not sure if the data will automatically refresh i.e. if a record was 1 and now in the list there is only 01, will the record automatically be associated with 01?

What records are you worried about?

 

[Micron]

Sounds like a lookup field in a table.
AFAIK, you cannot use the format function on a text field to pad it with zeros, so that won't work. But to show them without actually altering the data, use an expression in an unbound form control, like = "00" & [Field] where Field is the name of the control actually holding the data. However, the number of leading zeros could not be volatile without a lot of work (if at all). If you must have this field as text, then you will never sort on the values as numbers. 113 comes somewhere after 1, and 2 comes after 113. Your only hope would be a sort field that you add.