UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Reporting Sorting Based On Underlying Query, Access 2016    
 
   
hedge
post Aug 27 2019, 10:33 AM
Post#1



Posts: 506
Joined: 17-November 08



Hi,

I would like my report to sort the data in the same way the data is sorted in the underlying Query.

Is there anyway to accomplish this, without setting up specific sorts in the report.

The reason is, I have users who want things sorted different ways. So I was going to have the underlying query have no sort on it, so it shows up exactly how the user inputs each line.

But for some reason when I come to my report, it doesn't show in the same order as the query. I made sure I don't have any sort options selected in the report.

Any help would be great.

It is actually putting everything in the reverse order of the query (which might just be a coincidence)

thanks,
This post has been edited by hedge: Aug 27 2019, 10:34 AM
Go to the top of the page
 
projecttoday
post Aug 27 2019, 10:49 AM
Post#2


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


Try using the Order By and Order By On Load properties of the report.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Aug 27 2019, 12:56 PM
Post#3


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


Are there any lookup fields in the table fields that feed the query?
Go to the top of the page
 
hedge
post Aug 27 2019, 01:49 PM
Post#4



Posts: 506
Joined: 17-November 08



yes
Go to the top of the page
 
BruceM
post Aug 28 2019, 06:37 AM
Post#5


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


Lookup fields show one thing, but the underlying data is what Access "sees". Sorting is done according to the underlying value, not the apparent one. It is one of several reasons why most developers avoid lookup fields.

Best practice is to use a linked table rather than hiding what is going on.

I do not understand how the user's sort preference is to be applied to the report, nor why you do not wish to use the report's sorting. One option may be to alias the sort field(s), and sort on the alias name, but I will need a better understanding of how the user-specific sorting is carried out.
Go to the top of the page
 
arnelgp
post Aug 28 2019, 08:16 AM
Post#6



Posts: 1,425
Joined: 2-April 09
From: somewhere out there...


try adding code to the Report's Load event:
CODE
Private Sub Report_Load()
    Dim strSQL As String
    Dim strOrder As String
    Dim intPos As Integer
    Dim td As TableDef
    strSQL = Me.RecordSource
    If InStr(1, strSQL, "SELECT ") > 0 Then
        intPos = InStr(1, strSQL, "Order By")
        strOrder = strSQL
    Else
        On Error Resume Next
        Set td = CurrentDb.TableDefs(strSQL)
        If Err.Number <> 0 Then
            Err.Clear
            On Error Goto 0
            With CurrentDb.QueryDefs(strSQL)
                strOrder = .SQL
                intPos = InStr(1, strOrder, "Order By")
                End If
            End With
        End If
        Set td = Nothing
    End If
    If intPos > 0 Then
        strOrder = Mid(strOrder, intPos + Len("Order By"))
        strOrder = Replace(strOrder, ";", "")
        Me.OrderBy = strOrder
        Me.OrderByOnLoad = True
    End If
End Sub

This post has been edited by arnelgp: Aug 28 2019, 08:17 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
projecttoday
post Aug 28 2019, 08:25 AM
Post#7


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


Interesting piece of code. It looks like it sets the report's sort to the order in the query. The OP wants to allow the users to choose a sort. I would suggest OpenArgs for that.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 11:50 AM