troubleshooting Question

Access query sort not working

Avatar of CountryGirlMD
CountryGirlMDFlag for United States of America asked on
Microsoft Access
5 Comments1 Solution79 ViewsLast Modified:
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
PartID
RecID
PartName


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"
    Else
        strCurrentOrder = "ProductName"
    End If
    Call fRefreshSort
End Sub

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

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

Query2
Me.lstMW.RowSource = "SELECT Query2.* FROM Query2 ORDER BY " & strCurrentOrder
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros