Sort Order on Filtered Recordset

[Redbeard]

Hi zmdb

Sorry it took so look to get back, it took me a while to find time to work on this project. It has taken me a week but I am almost done normalizing the database. It has took quite a while as there were more fields then I realized that needed to be done. It seems to be working out quite well; however, I am having an issue with the new tables and filtering. When I did a filter on the Permanent Location Site field on my main form before, I would get my records in the order that the main form was sorted by, which is a unique number for each item (ascending). Now when I filter by the new Permanent Location Site field which is connected to a lookup table, the record are sorted in random order? I have tested this and it happens on all the fields that I have changed. If I do the same filter on the query that runs my main form it sorts correctly? Do you know why this is happening?

Thanks

[zmbd]

Moved this question from because we try to keep each thread to one topic when possible:
https://bytes.com/topic/access/answe...in-combo-boxes

By default the first field in the query tends to be the primary sort in ascending ((symbolic)(0-9)(A-Z)) order and that is most likely what you are seeing is that default sort on the first column. I'll need to see the SQL that you are using for the row source to be sure; however, the ORDER BY(read more) clause should handle the sorting.

For example, I have a serial number field on my lab equipment that once I filter down via manufacture, part/model... I use a combobox to filter on serial numbers (it's a quick and dirty search, if found then code moves to that record, if not in the list, then the event handles adding a new item :) )...

(I'm typing the following from memory... there might be a typo ;-) )

SELECT [tbl_inventory]![inventory_PK]
   , [tbl_inventory]![SerialNumber]
FROM [tbl_inventory]
WHERE ([tbl_inventory]![Products]=[zctrlcboproducts])
 ORDER BY [tbl_inventory]![SerialNumber];

Note that ascending is implied, one would append DESC for descending sort order and you can explicitly set ascending sort with ASC

[Redbeard]

Hi zmdb

I have tried to make this as clear as possible…

So, I have figured out what is happening but don’t know how to fix it. I have a Main Table and several “Look up” tables, as you suggested. I have a Main Form which I use to enter any new records, which is run by a Main Query that incorporates the Main Table and the Lookup Tables. The Main Query is sorted ascending by the Unique Code field in my Main Table, which is not the Primary Key for my Main Table.

So, when I open my Main Form the records appear in ascending order based on the records Unique Code. Now, if I right click in one of my combo boxes (Object Name) that is connected to a Lookup Table, and filter for Begins with “Book” then I should get all my records that start with “Book”, which I do. The problem is that they are not ordered by the Main Query that is sorted ascending by the Unique Code.

If I click on the “Advanced Filter/Sort” on the Access Ribbon it pulls up the query I have asked for which just show the “Object Name” Lookup Table with the criteria of Like ”Book*” and no sort. So I checked out the Object Name field to see if that was causing the problem. It is sorted by Object Name in ascending order so that when a new name is entered into the table it will come up in the combo box in alphabetical order not at the bottom of the list. However the records do not come up in order of Object Name either. For example “Book*” should return results of all the records with “Book” then “Bookcase” then “Bookmark”, etc… but it does not.

After extensive testing I have found that they come up in order of the Primary Key of my Main Table? So, to fix this I added to my Main Form in the Order By Property, to sort by the Unique Code figuring that this will fix the problem.

Now what is happening is when I open the database and do the sort on the Object Name for Begins with “Book”, I still get it sorting by the Main Tables Primary Key and not the Unique Code. However, if I open the “Advanced Filter/Sort” on the Access Ribbon it pulls up the query which now shows that the Unique Code has been added and it is sorting in ascending order, however it does not actually do that. So then, if I run that query from the Advanced Filter/Sort”, it now works, and all other filter in any other field now will sort properly… until I close the database and reopen. Then I have to run the Advanced Filter/Sort” first before it will work again.

Any idea why it defaults to the Primary Key in the Main Table on open, until I run an advanced filter then it works fine? Even though both my Main Query and my Main Form are both set to sort by the Unique Code field.

Thanks

[NeoPa]

RedBeard:
Now, if I right click in one of my combo boxes (Object Name) that is connected to a Lookup Table, and filter for Begins with “Book” then I should get all my records that start with “Book”, which I do. The problem is that they are not ordered by the Main Query that is sorted ascending by the Unique Code.

The fact that you see the main sort order that you expect is almost a random occurrence. There is no valid reason to expect that unless you know how it works, which seems doubtful considering some of your comments. Let me try to explain.

The sort order of the query is almost irrelevant. It may be effective on rare occasions where no other factors come into play, such as when you originally open the form.

Filtering by a specific field is not a guarantee of the results being sorted that way either. It just happens that no stronger indication is given for the sort order so Access is left to use whatever suits it best. In this case a simple sort by the filtered field is required to apply the filter and nothing tells it otherwise so it shows the data in the order that comes through. By this point the sort order of the query is entirely irrelevant and superseded. The sort order won't be 1=Filtered field; 2=Unique Code, but simply 1=Filtered field. Just as you're seeing, it would seem. Entirely to be expected in the circumstances.

Access doesn't automatically guess what order you want the records shown when an Access-level filter is applied. That is to say when a filter is applied using the interface provided by Access itself, rather than by you as the designer/programmer. To get what you want from this you'll need to add code to set the following four properties of the form :

.Filter
.FilterOn
.OrderBy
.OrderByOn

Unfortunately, you will also need to design an interface that allows you to determine what the user wants in the way of a filter, which can often be somewhat less than straightforward.

NB. The .OrderBy & .OrderByOn properties are what Access uses to determine how it is you want the records sorted. If these aren't set then it will assume you don't care and it will work its magic. Then, what comes out is what's left when it's done what it needs to do.

[Redbeard]

Thanks for the Help NeoPa

Apparently all I need to do was turn the Order By On Property to “Yes” as I had already set the Order By Property to the filter I wanted. Almost had it!

My confusion comes from using the Access-level filter to filter on a field that is in my Main Table instead of a Lookup Table. When you do that it retains the sort order of the Query that runs the Main Table and you do not need to set the Order By or Order By On Properties. As before I Normalized my Database all the fields that I normally sort, were in my Main Table and not Lookup Tables.

Thanks again for the help and detail explanation.

[NeoPa]

I'm so pleased to hear that. Well done :-)