I had a weird issue with my records not sorting, which I cannot replicate.
I have a unique field, ID, in the format AAA0000 or AAAA0000. I had gone back and forth between three and four digits for the numbers using the replace feature (calculated formula along the lines of "Left ( ID ; Length ( ID ) - 4 ) & Right ( ID ; 3)", and I have some in the 1000 range and some in the 0000 range (fewer than 99 each, so only the last two digits change).
I had entered the ones in the 1000 range first using 0001, 0002, then added the prefixes, then replaced the initial 0 with a 1. Then I created records for the ones in the 0000 range.
For some reason, when I used the Sort feature to sort by ID ascending, it sorted the 1000s first, before the 0000s. (And yes, I confirmed each group was sorted ascending). I did this multiple times.
When I went into Table view (instead of Form view) and tried again, the records were still displayed in this bizarre order. However, sorting while in table view got the records to sort properly.
I have no clue why or how this might have happened, or what I did that would allow someone to recreate it. I tried a very basic example in a clean database but couldn't repeat it (though I did some of the testing in table view which may have negated the problem). Hopefully someone can figure out what is going on there.