Forum Discussion
Sort in Query not Displaying
I had time to review this again. I think I spotted the reason this does not work. Your screenshot does not show that you actually removed the Lookup and replaced it with a normal number field containing the foreign key. The screenshot shows the Lookup field in the properties DISPLAYS only a textbox. That means the problem remains in the Lookup itself.
One of the reasons so many seasoned Access developers strongly recommend against use of Lookup fields is problems like this one are so hard to resolve for less experienced developers. Sure, they look snazzy. And if you really know what you're doing, they can be implemented with minor difficulties. In most cases they create more complications than they are worth.
I recommend you create a new field for Members. Make it a number of Long Integer type. Do not use the Lookup Wizard to create it.Update it with the MemberID field corresponding to the current values in the original field. Then delete this Lookup field.
Handle the row source for a combo box on the form instead.
Consider this:
- Open your report in Design View.
- Go to Group, Sort, and Total (from the Design tab).
- Add a Sort on the field that indicates voting eligibility (e.g., CanVote DESC) and then by MemberName ASC.
Tried that, but it didn't work.
I did some more digging, and it's actually my table that isn't sorting right. When I go to design view for my table, in LookUp, my Display Control is: Combo Box and my Row Source is a query. The custom sort is done in the query. When I go to Datasheet View, the custom sort I did is ignored.
The problem originates in the use of a Lookup field in the table. While it is supported in Access tables, and very few other databases, using an interface feature like a Lookup field in a table is generally a good way to create problems like the one you face here. It's a mash up of tables and interface that simply leads to complications best avoided in the first place. Unless you have a very simple database indeed.
The more appropriate, more easily managed approach is to keep interface features like combo boxes, or lookup fields, in forms, where they belong.
I suggest you convert the lookup field in the table to a number of long integer data type. In that field, you store only the Foreign Key values for the related records.In the combo box on the form, create the sorted row source to display to the user.
Finally got a chance to try out your suggestion, which makes perfect sense! Unfortunately, it didn't work. I went through every table to make sure the DisplayControl in LookUp was set to 'Text Box'. Ran the Compact and Repair Database, restarted Access, but it still won't work. :(
You asked for screenshots, so here's what I have:This is the table that lists all of the members information. Very straightforward.
lstMembers
I then have a query to filter and organize the information in this list:
qryMembers2
SQL:
SELECT IIf([MemberTitle]<>"Staff" And [MemberTitle]<>"NRCS",1,IIf([MemberTitle]="Staff" Or [MemberTitle]="NRCS",2)) AS MemberSort, lstMembers.MemberInitials, IIf(IsNull([MemberMName]),[MemberFName] & " " & [MemberLName] & " — " & [MemberTitle],[MemberFName] & " " & [MemberMName] & " " & [MemberLName] & " — " & [MemberTitle]) AS Member, lstMembers.MemberFName, lstMembers.MemberMName, lstMembers.MemberTitle, lstMembers.MemberLName, lstMembers.MemberTRC, lstMembers.MemberID
FROM lstMembers
WHERE (((lstMembers.MemberTitle)<>"Former Staff" And (lstMembers.MemberTitle)<>"Former Associate Director" And (lstMembers.MemberTitle)<>"Former Volunteer") AND ((lstMembers.MemberTRC)=True))
ORDER BY IIf([MemberTitle]<>"Staff" And [MemberTitle]<>"NRCS",1,IIf([MemberTitle]="Staff" Or [MemberTitle]="NRCS",2)), lstMembers.MemberLName;Now for the form. I have a checkbox which I can click all members who attended the meeting (just showing the number of those members for safety reasons)
frmReportTRC
These numbers are in the order I want; 22 should come before 6. However, when I click 'Ok' this is the order it displays:
This is the query used for the form's checkbox:
SQL:
SELECT qryMembers2.MemberID, qryMembers2.MemberTitle, qryMembers2.MemberInitials, qryMembers2.MemberLName, qryMembers2.MemberSort, qryMembers2.MemberLName
FROM qryMembers2
ORDER BY qryMembers2.MemberSort, qryMembers2.MemberLName;
Now, frmReportTRC is connected to a table called tblReportTRC. This table temporarily stores the information for the report. This is the table that had the lookup fields that I cleared out. If you need to see this table, please let me know.Please let me know if you need any additional information from me. Again, GREATLY appreciate your help and any advise!
Thank you for the advise. I'll look into it and get back to you on the status..
Changing the Lookup fields in a table to show a textbox doesn't address the real problem, so I would not expect that to make any difference. The problem is the use of the Lookup in a table. Once that is changed, and your selections are properly sorted in the rowsource for the list box on the form, you should see the appropriate results.