Indexes in table causing sortation to not work?

[mhcruella]

Goal: I am using code to make up an SQL statement that will append information to a temporary tbl (tblTemp) in a specific order. I want to base a report (rptlabels) off of the data in the tblTemp and keep these records in order (alpha, zip or numeric).

Problem: The report will generate in the correct order, but will skip fowards and backwards in groups of numbers. For example, if I have records numerically numbered from 1 to 400, the report will print records 1-200, go back to 100, and then print to 350 and so on. I looked at the tblTemp and found that there were many indexes on the table.

I removed the indexes on tblTemp, but they reappear again. When the indexes are removed, the report prints in the order it is supposed to all through the report. When the indexes reappear, the jumping in order starts.

Question: Is there a way through code to check to make sure that there are no indexes on a table? When I open the report using docmd.openreport, I again use the srto order. Could there be another way to ensure that the order I want the records in is maintained?

Thanks for your help.

 

[willir]

mbcruella

Do you see something like...
1
11
12
2
21
22
3
4
...this is how sorting is done if the "numbers" are being treated as text strings.

Next regardless of how the data is stored in the table, you can control the output using the ORDER BY clause in the SQL SELECT statement. For example, SELECT * from tblTemp ORDER BY Your1stOrderField, Your2ndOrderField, Your3rdOrderField

Why your indexes are re-created? Not sure. Do you delete and re-create the temp table? Does the way you re-create the temp table create the indexes? Access may index numberic fields.