auto numbering sort wrong

[tjones]

What do I have to do to the table design to make the auto generating number sort as 1, 2, 3..10,11, 20 etc> right now it is sorting 12,12...2.28, 5 , 6 etc.

Also how do you make it sort on first one field then a second field?

Sorry these are probably basic questions but I am struggling to teach myself access.

 

[pbaldy]

It sounds like the data type of the field is Text, I would expect Autonumber. If it is Autonumber, you must be using a function or something that is causing it to be treated as text.

Sorting is done left to right, so the first field listed in SQL view or the leftmost field in design view is the primary sort.

 

[tjones]

That is what it is doing sorting 1, 11 12....2, 21, 22 etc. How do I make it so 1-9 are set as 01-09? That would accomplish the correct sort order

 

[pbaldy]

You seem to be asking for a specific answer to a generic question. You haven't mentioned the data type of the field. Generically, you either change the data type of the field to a numeric one, use the appropriate conversion function (CInt, CDbl, etc) to sort on, or format to a specific length with the Format function:

Format(FieldName, "00000")

 

[tjones]

It is an auto generating number field. right now it is sorting left most.

1, 11, 12 .... 2, 20, 21 etc.

I need it to sort 1-9, 10, 11 etc sequentially.

incidentally this question is tied to a query question that shows the results where I would like to sort on this number field. I would link there if i could get that to work.

 

[boblarson]

It is an auto generating number field.

Paul has asked you twice what data type. You keep saying "auto generating number field." Does that mean that the datatype says, specifically - AUTONUMBER? Or are you using a LONG INTEGER, or TEXT datatype along with code to get the Max number and add one (i.e. DMax + 1)? It sounds like the Dmax + 1 and using a TEXT field which is why it is using Text sorting.

 

[tjones]

I did not understand what you were asking. Sorry, Yes it is a LONG INTEGER.

 

[boblarson]

You need to then understand that tables don't keep data in a particular order. When you put data into a table, Access will store that information where it pleases. In order to have "order" to the table you should use a QUERY with an ORDER BY clause. And if you want to view the table (when you look at the contents of a table you aren't really looking at the table itself, but a system query) in a sorted fashion, you can use the ORDER BY property of the table. But realize that won't carry over into any other object (form, report, query, etc.).

 

[tjones]

I understand about the table, I was trying to organize the report. linked to the query you have been helping me with. Where it is putting the numbers in the dept id field, if i get them sorted 1-10 etc they will be in the correct order on the report. but of course first i have to get the query / report working.

 

[Pat Hartman]

Reports build their own queries so regardless of how you sort a query used as the Report's RecordSource, it doesn't carry forward. You have to open the report in design view and specifically set the report's sort property.

Another FYI regarding report's RecordSource queries. Not only is the order by clause ignored, all columns that are not bound to controls on the report are also dropped. So if you select a,b,c, and d but only bind a, b, and c to controls, d will be dropped and if you have written code to use it, the code will fail at runtime. The only way to get around this is to create a hidden control so you can bind d also.