Report records Orderby code (1 Viewer)

naobao

Registered User.
Local time
Today, 16:14
Joined
Feb 13, 2014
Messages
76
Hi,

I want to use the following code,
to sort the records from a field value,
but it not work!
Please help to fix it!

Code:
Private Sub Report_Open(Cancel As Integer)
If Me.E = "1" Then
DoCmd.SetOrderBy "C ASC, A DESC"
Else
DoCmd.SetOrderBy "C DESC,"
End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:14
Joined
May 7, 2009
Messages
19,169
move your code to Load event.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:14
Joined
Jul 9, 2003
Messages
16,245
I've never done it that way I always provide the report with an SQL statement in its record source which shows the records I want, sorted how I want.

See video number 2 here:-

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:14
Joined
May 7, 2009
Messages
19,169
Code:
Private Report_Load()
With Me
    If Nz(!E, "9") = "1" Then
       .OrderBy = "C Asc, A Desc"
    Else
       .OrderBy = "C Desc"
    End If
    .OrderByOnLoad = True
End With
 

naobao

Registered User.
Local time
Today, 16:14
Joined
Feb 13, 2014
Messages
76
Code:
Private Report_Load()
With Me
    If Nz(!E, "9") = "1" Then
       .OrderBy = "C Asc, A Desc"
    Else
       .OrderBy = "C Desc"
    End If
    .OrderByOnLoad = True
End With
it work! Thank!!
 

naobao

Registered User.
Local time
Today, 16:14
Joined
Feb 13, 2014
Messages
76
I've never done it that way I always provide the report with an SQL statement in its record source which shows the records I want, sorted how I want.

See video number 2 here:-

Thank!!
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:14
Joined
Sep 21, 2011
Messages
14,048
I've never done it that way I always provide the report with an SQL statement in its record source which shows the records I want, sorted how I want.

See video number 2 here:-

Tony,
I *thought* that would be a logical way of doing it when I first started trying to use Access, but I found I had to do it in the Report itself.?
Was I missing something? :unsure:

In fact I just tried it again with a query sorted on Amount descending, and that does so correctly, but when I open the report which has that query as it recordsource, it is in the order of the source to that query?
 

Minty

AWF VIP
Local time
Today, 23:14
Joined
Jul 26, 2013
Messages
10,355
I think you have to specify the sort order in the report, as it creates its own "query" for the data.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 28, 2001
Messages
27,001
I have seen situations in which a report does not honor the order specified in a query. I had to add a sort in the report itself. Since I have never tried doing really fancy reports, I can't swear that it would NEVER honor the original query order, but Access did ignore the order I gave it in the record source.
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:14
Joined
Jan 23, 2006
Messages
15,364
My understanding is that reports have their own sorting and grouping that supersedes any ordering in the record source.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:14
Joined
Feb 28, 2001
Messages
27,001
We probably come to that understanding from different directions, but I agree with you jdraw.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:14
Joined
Sep 21, 2011
Messages
14,048
I have seen situations in which a report does not honor the order specified in a query. I had to add a sort in the report itself. Since I have never tried doing really fancy reports, I can't swear that it would NEVER honor the original query order, but Access did ignore the order I gave it in the record source.

My understanding is that reports have their own sorting and grouping that supersedes any ordering in the record source.
Well I am going to stick my neck out here and say 'not even supercedes' as I tried a query with a sort order and the report just shows the query data, but in the order of the table that was the source for the query. ?

I remember this catching me out when I first started with Access as eveything else I had played with respected the order of the data supplied, but then they did not have their own sort function (or so I recall).?
 

naobao

Registered User.
Local time
Today, 16:14
Joined
Feb 13, 2014
Messages
76
Code:
Private Report_Load()
With Me
    If Nz(!E, "9") = "1" Then
       .OrderBy = "C Asc, A Desc"
    Else
       .OrderBy = "C Desc"
    End If
    .OrderByOnLoad = True
End With
If I orderby 3 fields like this
Code:
With Me
    If Nz(!E, "9") = "1" Then
       .OrderBy = "C Asc, A Desc, B Desc"
    Else
       .OrderBy = "C Desc"
    End If
    .OrderByOnLoad = True

End With
the code not work...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:14
Joined
May 7, 2009
Messages
19,169
which Column is not Sorting?
do you have number on them?
can you show some of its data/].
 

Users who are viewing this thread

Top Bottom