We help IT Professionals succeed at work.

Access query sort not working

Last Modified: 2019-03-19
I have a form that has a list box – there are two queries that serve as the data source for the list box
The users can switch between the data sources by clicking on an option on the form
Above the list box there are label fields that act as column headers and sort the data displayed in the list box
The first query works with no issues, the user can sort the data in the list and apply various filters
The second query has no additional filter options but I do want the users to be able to sort the list by the same options as the 1st query
The sort options work on all the columns of the second query except the ‘product description’ field.
The ‘product description’ field is the only long text field in the queries
The only difference between the queries is the 2nd used to exclude records from the 2nd query.
When you click on the ‘product description’ sort button it does change the order of the data but does not sort it correctly, if it is somehow sorted by some other field it’s not obvious, it does arrange the data differently ascending vs descending and they are always in the same order.
I’ve tested the 2nd query by adding the sort option on the product description in the query itself – it doesn’t work there either.  It seems to just be the combination of the long text field & the no records in the 2nd table.
Can anyone tell me why the sort on the 2nd query / ‘product description’ doesn’t work and how to fix it?

Here’s the code:

1st table -- tblProducts      
RecID      Long Integer
DateReceived      Short Date
ProductName      Text 255 char
ProductDescription      Long Text
Manufacturer      Text 100 char
ProductType      Text 50 char
ProductNumber      Text 15 char

2nd table -- tblSubParts

Query 1 – this is just a single table query
SELECT tblProducts.RecID, tblProducts.DateReceived, tblProducts.ProductName, tblProducts.ProductDescription, tblProducts.Manufacturer, tblProducts.ProductType, tblProducts.PartNumber FROM tblProducts;

Query2 – there are two tables here – showing the same table/fields as Query 1 only where there is no record in the second table
SELECT tblProducts.RecID, tblProducts.Received, tblProducts.ProductName, tblProducts.ProductDescription, tblProducts.Manufacturer, tblProducts.ProductType, tblProducts.PartNumber, tblSubParts.PartID
FROM tblProducts LEFT JOIN tblSubParts ON tblProducts.RecID = tblSubParts.RecID
WHERE (tblSubParts.PartID Is Null);

There is a global variable on the form strCurrentOrder that is used to remember the current sort order
This is one of the sort buttons

Private Sub lblSortby_ProdName_Click()
    If strCurrentOrder = "ProductName" Then
        strCurrentOrder = "ProductName DESC"
        strCurrentOrder = "ProductName"
    End If
    Call fRefreshSort
End Sub

The fRefreshSort function resets the Row Source depending on which query is being used

Me.lstMW.RowSource = "SELECT Query1.* FROM Query1 " & fFilter() & " ORDER BY " & strCurrentOrder

Me.lstMW.RowSource = "SELECT Query2.* FROM Query2 ORDER BY " & strCurrentOrder
Watch Question


the best way to debug it would be first put a break point at line:

Me.lstMW.RowSource = "SELECT Query1.* FROM Query1 " & fFilter() & " ORDER BY " & strCurrentOrder

and see what is being returned.

you may also add:


after the line: Me.lstMW.RowSource
Most Valuable Expert 2015
Distinguished Expert 2018
This one is on us!
(Get your first solution completely free - no credit card required)
Fabrice LambertConsulting
Distinguished Expert 2017

I'm nowhere near conviced that sorting on a product description make any sens at all,.
Plus long text (or memo) type columns are somewhat capricious beasts, better not ask them too much beside the basic (retrieving data).

Side notes:
I don't think you need global variable for such trivial tasks, a form level private variable is enough.
Give up hungarian notation, it provides nothing usefull.


Thanks All
Ryan - I had already done that - it didn't give any clue to why query1 worked and query2 didn't -- query 2 never gave me an actual error - it just didn't sort the data correctly

Gustav - I went with a simple version of your solution -- just a single left(ProductDescription,255) because very unlikely in my data for the first 255 to not be unique -- of 66,000 records -- only about 30 are over 255 -- max len 628

but it still bugs me that query1 will sort by the long text field and query2 won't

Fabrice - not my choice to sort by long text -- it's a user requirement -- and my bad -- I referred to the strCurrentOrder variable as "global" when it was actually a form level variable like you suggested
Most Valuable Expert 2015
Distinguished Expert 2018

but it still bugs me that query1 will sort by the long text field

It won't. If it seems to do, it is pure luck.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.