>> and the order is very close (but not exactly the same) <<
Did you check your data for Zero Length Strings? ... Nulls will bubble up to the top with the sort order specified. But, it may be important to note that ZLS's come AFTER Nulls in the sort order.... So ... if you have a F_Name and L_Name that are both ZLS's they will come AFTER a record with F_Name and L_Name set to Null.
That may indeed be the case, but the actual result appears to be moot because Access seems to ignore the ORDER BY statement.
>> HOWEVER, On the Access Side, it does not appear to sort at all. The records are presented sorted by the Person_ID (Which is the PK for the table) instead of by the ORDER BY statement. <<
How are you displaying the data? in a Form or in the Datasheet view of the Query Object? ... or a Datasheet view of the linked table that is bound to this view? ... Either way, each one of those objects has a property named "OrderBy" that, if set to something, will be applied AFTER the records are retreived ...
For the purpose of verification only, I am using a datasheet view. The application uses the query as the source behind a combo box that allows the users to select a customer name. The dropdown list displayed by the combo is also out of order in the same way that the datasheet is.
Please check the properties of all those objects to ensure there is nothing set in the OrderBy PROPERTY ...
TableDefs: View in table design view (You will get a message telling you some properties can't be modified, click Yes to open to the design view anyway), when in design view, expose the Table properties (in A2003... View > Properties), you will see the OrderBy propery ... ensure its cleared. While you are in the linked table properties, check to see if there are any Indexes on it ... if so, let me know what they are, and if any are considered the primary key index.
QueryDefs: View the query in design view. Click the grey space where are the source tables are shown, then view the properties of the Query Object by selecting View > Properties ... ensure the OrderBy propery is cleared.
Forms: View the form in design view. Click on the grey space OUTSIDE of any section (ie: just beyond the form width) to ensure the Form is the object selected. Then view the properties of the form (View > Properties). The OrderBy property can be found on the "All" tab as well as the "Data" tab. Again ... ensure that this property is cleared ...
- I found an OrderBy Property in the Table tblPeople (ORDER BY tblpeople.Person_ID), and removed it.
- I did not see any OrderBy Properties in any of the SQL Server Views.
- I will be checking the remaining queries this afternoon after lunch.
- We will be doing more testing this afternoon after I have finished my search.