I have a table, Tickets, with about 8000 entries in it. Today, I was working with a form that is bound to a query which is bound to the table. While I was filtering a specific group of numbers, I noticed one record was not in sequential order with the rest of the retrieved records. I was wondering what would cause this. I tried removing all sorting levels and re-sorting the data but this on record was always out of order. To fix the problem, I not only had to re-create the table but also the query the form uses. If anyone has any advice to why this would occur and how to prevent it from happening again, I would be truly grateful.
Originally posted by jmrSudbury
Is it out of order in the query? By what field do you sort in the query? Is it the first field in the query?
The record is not out of sort in the query. In the query, the records are sorted according to the primary key. While looking for solutions to the problem, I deleted the sort orders in the underlying query. This one record was still out of place (numerically) when we filtered records. Fixing the problem was a headache and I was wondering how to avoid such a situation again.
When you apply your filter, is there anything in the Order By property? Setting the Order by property to the sorting you need should fix this. As well, to prevent access from pulling up data in the order in which it was entered, try compacting the back end. According to Microsoft, this will reorder according to your index.
The order by property was not originally set. While I was trying to solve the problem it was set to the primary key. Again, the one record was out of numerical sequence. I will try compacting the backend....I do not remember if I did try compacting the backend. Thanks for the suggestion.