473,388 Members | 1,970 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

Sort Order on Filtered Recordset

112 100+
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
Jan 18 '16 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. .Filter
  2. .FilterOn
  3. .OrderBy
  4. .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.

5 2790
zmbd
5,501 Expert Mod 4TB
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 ;-) )
Expand|Select|Wrap|Line Numbers
  1. SELECT [tbl_inventory]![inventory_PK]
  2.    , [tbl_inventory]![SerialNumber]
  3. FROM [tbl_inventory]
  4. WHERE ([tbl_inventory]![Products]=[zctrlcboproducts])
  5. 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
Jan 18 '16 #2
Redbeard
112 100+
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
Jan 19 '16 #3
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. .Filter
  2. .FilterOn
  3. .OrderBy
  4. .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.
Jan 19 '16 #4
Redbeard
112 100+
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.
Jan 19 '16 #5
NeoPa
32,556 Expert Mod 16PB
I'm so pleased to hear that. Well done :-)
Jan 19 '16 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: One's Too Many | last post by:
Ran into a strange problem today: 8.1.7 on AIX 4.3.3 Database and applications had been working fine for two years and all of a sudden a couple of regularly-run queries are now no longer...
2
by: Alpay Eno | last post by:
Hello all... I'm using asp to get records from an access database, very similar to the way datagrid would work. The title of each column in my table is a link that alternates the sort order between...
4
by: Jon Hunt | last post by:
Please forgive a newbie question. In MSSQL I can execute a stored procedure (sp_helpsort) that returns the sort order (listing characters) of a database. Is there a DB2 equivalent to this query?...
2
by: tom r. | last post by:
I have a table with records in a desired sort order (I appended them to the table in the order I wanted). It would look something like this: Table 1 Name Date Code ...
3
by: Bob Dankert | last post by:
Is there any way to maintain the sort order of a sort on a 2D array? For example: I have the array: 1,a 2,a 3,f 4,a 5,s 6,a 7,z 8,b and sort it by the second column, and I...
7
by: Steve Crawford | last post by:
I am suffering some sort order confusion. Given a database, "foo", with a single character(4) column of data left padded with spaces I get: select * from foo order by somechars; somechars...
2
by: adrian.chandler | last post by:
Hi all, I have been using letter and symbol codes such as GNU< GNU\ GNU} GNUł in an Access table. I was surprised to see that when the table was sorted on this field, the order is: GNUł...
9
by: phillip.s.powell | last post by:
Ok, you have three tables. You're supposed to be able to not only sort (ORDER BY) according to a_name, no problem, but you must also have the ability to sort (ORDER BY) the relationship between...
2
by: Chris | last post by:
Dear All, I have a subform in datasheet view whose record source is a table (not a query) A user can right click and customise sort order. Now when the form is closed VBA code saves for subform...
0
Seth Schrock
by: Seth Schrock | last post by:
Introduction There have been a few times recently that I have had to make it so that users could easily change the order of records. This comes in handy when planning tasks and you don't...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.