Report records Orderby code (1 Viewer)

[naobao]

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!

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]

move your code to Load event.

 

[naobao]

move your code to Load event.

after move to Load event,
when open report system warning no record!

 

[Uncle Gizmo]

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:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

 

[arnelgp]

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]

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]

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:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

Thank!!

 

[Gasman]

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:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

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?

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]

I think you have to specify the sort order in the report, as it creates its own "query" for the data.

 

[The_Doc_Man]

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]

My understanding is that reports have their own sorting and grouping that supersedes any ordering in the record source.

 

[The_Doc_Man]

We probably come to that understanding from different directions, but I agree with you jdraw.

 

[Gasman]

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]

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

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]

which Column is not Sorting?
do you have number on them?
can you show some of its data/].

 

[naobao]

which Column is not Sorting?
do you have number on them?
can you show some of its data/].

Sorry! It is my mistake.