Numbers in Ascending Order

[CassandraB]

Hi,

In my table have serial numbers from 1 through 99999 (example 34, 989, 12938 .....etc) but when I put them in ascending order it will put them in 12938, 34, 989 ... order. I think it sees the numbers as 12938, 34000, 98900 .... . I would like to place 000's in front of the 2/3/4 digit numbers so it reads them as their true numbers. The data type for the serial numbers is text (which I need to keep).

Does this make any sense? Can anyone help? Thanks in advance.

 

[WayneRyan]

Cassandra,

You can use this in the BeforeUpdate of your contgrol (if user
types in), or the BeforeInsert event of the form if the user
does not access this field.

Me.MyNumber = Format(Me.MyNumber, "00000")

Wayne

 

[dcx693]

WayneRyan, the user did not mention using the table data in a form, even though we are in the Form section of the forums. Also, your routine will change some of the table entries, but it won't change them all. She will need to use an update query if she wants to permanently change the text to having leading zeroes, or use a query to pad them or use the Val() function to sort them correctly numerically for use in a form.

 

[CassandraB]

Yes, it will be used in a form....
The existing data does not have the leading 0's that I want them to have. Since I am new at this... how would I use a query to pad it or use the update query? Your help is much appreciated!

 

[Rich]

Couldn't you just use CDbl on the field and order it Ascending?

 

[dcx693]

Rich the CDbl function should work just as the Val function (which I suggested) should. It's up to the user to decide if they want to change the original data permanently or not.

If yes, I'd just use an update query to change them to include leading zeroes using an expression like the one WayneRyan suggested: Format([yournumberfield],"00000"). The poster said she preferred keeping the original data in text format.

If the poster does want to keep the original data, then I'd use a numeric conversion formula to pull the value out of the text string and sort on it.

 

[CassandraB]

Under what property would you put the CDbl?

 

[dcx693]

Which way do you want to go? Alter the original data, or not?

 

[CassandraB]

Yes, alter the original data and any new future data. Thanks.

 

[dcx693]

Then you need to run an update query against the original data in the table. If you want to keep those numbers as text, then use the expression Format([yourfield],"00000") in the Update To: line of the update query.