Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10529

Re: Trying to set sort order in a Select statement

Show all headers | View raw

On 18/03/2015 23:00:05, KW wrote:
> On Wed, 18 Mar 2015 09:31:35 GMT, "Phil" <phil@stantonfamily.co.uk>
> wrote:
> 
>>On 17/03/2015 22:02:12, KW wrote:
>>> Regarding Access 2010.
>>>
>>> I am trying to set a dynamic sort order in a subreport but am having
>>> problems.
>>> The Recordsource property of the subreport is blank
>>> I have an OnOpen event in my subreport that looks like this:
>>>
>>> If DLookup("[RateDisplay]", "[tlkpSystemDefaults]") = "DESCENDING"
>>> Then
>>> Me.RecordSource = "SELECT qrySubReservations.* FROM
>>> qrySubReservations ORDER BY qrySubReservations.ratesum DESC;"
>>> End If
>>> If DLookup("[RateDisplay]", "[tlkpSystemDefaults]") = "ASCENDING" Then
>>> Me.RecordSource = "SELECT qrySubReservations.* FROM
>>> qrySubReservations ORDER BY qrySubReservations.ratesum ASC;"
>>> End If
>>>
>>> In DEBUG, I know the above lines of code are being executed.  However,
>>> the ORDER BY clause is not being considered.  I have played with the
>>> Order By On Load Yes/No properties in both the report and subreport
>>> but nothing I do makes a difference.
>>>
>>> Any help would be appreciated.  Thank you.
>>>
>>> -Kristin
>>>
>>
>>I have seen this sort of thing before, and the cause has been that the
>>RecordSource is not blank. Check that if you open the subreport in Design
>>View that it is blank, and as an extra precaution, when you close the report,
>>explicitly set the RecordSource = ""
>>
>>Phil
>>
>>---
>>This email has been checked for viruses by Avast antivirus software.
>>http://www.avast.com
> 
> Phil,
> 
> Thank you for your reply.
> 
> 1.	 Yes, the Recordsource is blank
> 2.	 Yes, there is no sorting on my subreport, but this is sorting
> and grouping set in my report
> 3.	 I went to Allen Browne's link and inserted a line of code in
> my subreport like this:
> Me.GroupLevel(0).ControlSource = "LastName"
> 
> I receive an error - "There is no sorting or grouping field or
> expression defined for the group level number you used."  Not sure
> what that means.
> 
> -kristinw
> 

Patrick is right. Use
"SELECT qrySubReservations.* FROM qrySubReservations ORDER BY
qrySubReservations.ratesum; as your recordsource Nothing about ascending or
decending Create your report and add a sort on RateSum (This is GroupLevel(0)

Then on the OnOpen of the report you want 

Private Sub Report_Open(Cancel As Integer)

     If DLookup("[RateDisplay]", "[tlkpSystemDefaults]") = "ASCENDING" Then
           Me.GroupLevel(0).SortOrder = False     ' Ascending
    Else
          Me.GroupLevel(0).SortOrder = True      Decending
    End If
    
End Sub

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Trying to set sort order in a Select statement KW <noemailaddress@....> - 2015-03-17 16:02 -0600
  Re: Trying to set sort order in a Select statement "Phil" <phil@stantonfamily.co.uk> - 2015-03-18 09:31 +0000
    Re: Trying to set sort order in a Select statement "Ron Paii" <None@none.com> - 2015-03-18 06:59 -0500
    Re: Trying to set sort order in a Select statement KW <noemailaddress@....> - 2015-03-18 17:00 -0600
      Re: Trying to set sort order in a Select statement "Phil" <phil@stantonfamily.co.uk> - 2015-03-19 10:09 +0000
        Re: Trying to set sort order in a Select statement KW <noemailaddress@....> - 2015-03-19 12:19 -0600
    Re: Trying to set sort order in a Select statement KW <noemailaddress@....> - 2015-03-24 14:34 -0600
  Re: Trying to set sort order in a Select statement Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-03-18 06:32 -0700

csiph-web