I am working with an access data base and have a problem. in the table the forms are sorted in numeric order?

Question

but in the form view they are all messed up. It appears to be ordering them in a numeric order such as this:
1,10,100,101,102,-----2,20,200,201,202 etc etc. Is there a way to change the sort so that it will simply go 1,2,3,4,5,6,7,8,9??? Thanks!

Answer 1

Assuming your forms Record Source is the table name, try replacing it with the following:



SELECT * FROM [table name] ORDER BY Value([field name]) ASC



[field name] should be the field you mentioned above.

It seems that your field name is actually a text field (this is why it's sorting the way it is), the Value() function will convert it to a numeric value.



Record Source can be found if you "right click" outside of your form in design view, select Properties, and select the Data tab.



The Data Analyst - http://www.squidoo.com/thedataanalyst

Answer 2

Records in tables are not stored in numeric order, text order, or in any particular order. Rather, they are stored in random order and you should not count on them being in anything but random order.



Further, there are few absolutes, but here's one: you should never use a table as a form's record source. And rarely the name of a [stored] query.



The recordsource setting could be "SELECT Whatever FROM MyTable ORDER BY Val(SomeField)", and that would likely get you what you want. And it is Val, not Value.

Answer 3

Leading zeros. Format the fields in the tables to actually hold 0001, 0002...0010...0100... etc so that they will sort properly, but then set it to display ###0 in the form so the zeroes do not show.