I have encounter an odd problem with a specific field which has the Field Name: Billing and is Datatype: Short Text. When I try to sort alphabetically the result on the Report is actually based on a corresponding field that is datatype Autonumber and also serves as the tables primary key.
All other fields in the report sort just fine.
I would like to think I have tried the usual corrections, but may have missed one and I am hoping someone can point this out if that is the case.
Generally I would go into design view of the report, select the field on the Report, click the 'Group & Sort' button on the ribbon and make my selection for how I want to sort from there.
How I know that it is the Autonumber field that is being sorted is that when I switch from A-Z to Z-A, the top result when Z-A is in fact the final entry in the table and concurrently the highest number in the Autonumber and primary key field.
I cannot see how these two fields are linked or why the Billing field is sorting based on the Autonumber field. I have removed the primary key and still encountered the same problem.
A final note that may help point me in the right direction. When I go to the table that holds the field in question I've noticed on Datasheet View there is no option to sort this field. All other fields have the option to sort by clicking the black triangle, but this field does not have this option. I believe the sorting does take place on the Report, but I found it odd that all the other fields could be sorted in Datasheet View, but not this field. Any help is greatly appreciated. Thanks
UPDATE
So a (hopefully) short update to this situation. The solution I undertook was as follows.
Exported the table with the sorting problem to excel. From there undertook the process of duplicating the table with no data in it and then ran an append query to put the data back into the table.
This worked! … kind of.
I had suspected since I began trying to fix this issue that the sort was being affected by the primary key number. I feel this has been confirmed, but it has created a new issue.
What has occurred now is that a relationship query no longer functions because the fields are no longer the same type. One field is an autonumber primary key while the other field is short text. I can only conclude that the field prior to the excel export/import was data type number, even though the data within the field was all text and no numbers. But, it was necessary to have this field as a number so that the relationship to the autonumber field in the other table would function.
Overall it is a small victory in that the report that I first noticed the error on, the error being it would not sort in proper alphabetical order, is now sorting properly. This is an important report that is used often so I needed to get it working properly to move things forward. Unfortunately I have now entirely lost the use of a different report because of a ‘Type mismatch in expression’ which I am concluding is the autonumber to short text relationship. So I am going to post a new question about relationships and datatypes to see if there is someone that can assist in getting this fully functioning again. I hope this helps others who have encountered this problem.
