ORDER BY will not work in report's Me.RecordSource (1 Viewer)

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
Im using the report's Open event to set the report's record source.

I can set the "FROM" and "WHERE" clues, but the "ORDER BY" will be ignored :banghead:

Code:
Me.RecordSource = "SELECT * " & _
    "FROM [MyTable] " & _
    "ORDER BY [FieldName]"
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:37
Joined
Jul 9, 2003
Messages
16,244
Could it be that the order by clause is working correctly, but then the report is imposing some other sort order after you've loaded the records?

Sent from my SM-G925F using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 13:37
Joined
Jan 14, 2017
Messages
18,186
As UG suggested, check the Sorting and Grouping for the report.
That takes priority over any order by clause in the record source
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
Could it be that the order by clause is working correctly, but then the report is imposing some other sort order after you've loaded the records?

Sent from my SM-G925F using Tapatalk

Not that I could find

The report do have a RecordSource setting so I won't get errors for setting the fields in place, but it has no OrderBY.

Also if I msgBox the .RecordSource on the report's OnActivate event it shows correctly, but the report won't be sorted :confused:

It happen on several reports that I checked
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
As UG suggested, check the Sorting and Grouping for the report.
That takes priority over any order by clause in the record source

No sorting and grouping :(
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:37
Joined
Jul 9, 2003
Messages
16,244
No sorting and grouping :(
Don't know then, although maybe try the obvious, and put the SQL straight in a query and see what results you get.



Sent from my SM-G925F using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 13:37
Joined
Jan 14, 2017
Messages
18,186
OK in that case, apply your order by using the report sorting and grouping.
That will fix it . . . unless your report is corrupted
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:37
Joined
Jul 9, 2003
Messages
16,244
Oh the other thing, report events don't work as you might expect them to. In other words the load event the on open event and others can't change the record source as you would expect. In other words, the record source may be being changed too late, if you get my drift.

It took me a long time to work out how to use reports effectively and I've blogged about it here:-

http://www.niftyaccess.com/generate-multiple-reports/

it looks a bit complicated, and as it is in answer to a specific question, ferreting it out might be off-putting, but it's basically simple, you passed the record source into a property within the report, and then use the event to set the property to the record source.

You don't send the record source to the report, the report gets the record source from your form itself.

I found this approach solved a lot of problems.

Sent from my SM-G925F using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
26,999
Where you set the recordsource in your event code, just as an experiment, try adding a Me.Requery - because the OnOpen event has already opened the recordsource and started to impose whatever it is going to impose. Admittedly that might do nothing, but it is too simple to do it so why not try it? It is possible that changing the recordsource would automatically do a .Requery anyway, but heck, give it a shot.
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
Where you set the recordsource in your event code, just as an experiment, try adding a Me.Requery - because the OnOpen event has already opened the recordsource and started to impose whatever it is going to impose. Admittedly that might do nothing, but it is too simple to do it so why not try it? It is possible that changing the recordsource would automatically do a .Requery anyway, but heck, give it a shot.

I already did, but I get Run-time error '2467':
The expression you entered refers to an object that is closed or doesn't exist :confused:

Report will show the correct record source, but not the correct sort in it :banghead:
 

isladogs

MVP / VIP
Local time
Today, 13:37
Joined
Jan 14, 2017
Messages
18,186
Have you tried doing what I wrote in post #7?
 

Tieval

Still Clueless
Local time
Today, 13:37
Joined
Jun 26, 2015
Messages
475
Have you tried doing what I wrote in post #7?
Yes, Colin is correct, the lack of a sort order in the report sorting and ordering will overwrite your original on-load setting. I have had this in the past and despite the lack of logic, I found that giving in and setting the sort order in the correct place makes everything work fine.
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
OK in that case, apply your order by using the report sorting and grouping.
That will fix it . . . unless your report is corrupted

I do it in some reports and it works fine :)
In these reports I also use the grouping.
Here I only need sorting
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:37
Joined
Jan 14, 2017
Messages
18,186
The feature allows you to do either of both of sorting and grouping. In this case use it to set your sort order


Sent from my iPhone using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:37
Joined
Feb 28, 2001
Messages
26,999
Here I only need sorting

But surely that IS enough reason to use the grouping AND SORTING feature of report creation.
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
Thank you all for all the help :)

It seems reports will ignore the ""ORDER BY" part if .RecordSource is set :(
Using the .OrderBy will sort it correctly :) (Don't forget to set .OrderByOn = True)
 
Last edited:

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
The feature allows you to do either of both of sorting and grouping. In this case use it to set your sort order


Sent from my iPhone using Tapatalk
I know how to refer to GROUP level, but couldn't find how to refer to SORT level.
 

isladogs

MVP / VIP
Local time
Today, 13:37
Joined
Jan 14, 2017
Messages
18,186
I know how to refer to GROUP level, but couldn't find how to refer to SORT level.

Its in exactly the same place.
Click anywhere on your report and click Sorting and Grouping..
The S&G feature opens below your report with two choices: Add a group /Add a Sort.
Select the latter, choose your field. Ascending sort is the default but you can select descending if you wish. Repeat for additional fields if you wish.

That's it!
 

smig

Registered User.
Local time
Today, 15:37
Joined
Nov 25, 2009
Messages
2,209
Its in exactly the same place.
Click anywhere on your report and click Sorting and Grouping..
The S&G feature opens below your report with two choices: Add a group /Add a Sort.
Select the latter, choose your field. Ascending sort is the default but you can select descending if you wish. Repeat for additional fields if you wish.

That's it!
I know how to Group and Sort a report
I need to do it by code.
I know how to set the Group levels by code, but how can I set the Sort levels?
 

Users who are viewing this thread

Top Bottom