Why is that Query Sorts and Report Sorts sometimes produce anomalous results? For example, I have an inventory database in which the sort ascending by vendors produces a strange result, ie. All the vendors starting with B... come first then A, C, D, E .... etc all in the correct order. There is no leading hidden character or blank space. I have another client who needs to sort transactions by date and everything seems to work fine except October comes first then Jan, Feb, March, etc.
So what happens that causes these strange occurrences?
are you sorting what you think you are sorting?
for e.g. if your field in tblPurchases is a lookup to tblVendors and the actual data stored is the PK of the tblVendors entry but you have hidden the key field and you see the name of the vendor... what SHOULD access sort on if you sort on the lookup in tblPurchases: you can make a good case for both possibilities.
i must be into my n-millionth sort by now and i haven't seen an access error yet (different story with Excel by the way!)
try this - If you are sorting based on a query (in afor or report) use that query as a maketable query and use the new table as underlying source for the report - this should work.
There are Microsoft articless that explain why a query and report do not sort the same way on the same data.
So sort the query and create a table with it - this should solve the problem
I only reccommend this if we are not talking 500k records.
Also remember - a maketable query will write the table to the frontend db if this is a split database and other users will not see the table or be able to run the report, but they can run the procedure as well and then run the report.