ORDER BY Statement

[Dan Kerkman]

I"m using a SQL SELECT statement as the recordsource for a report. As long
as I do not include an ORDER BY clause the SQL string works fine but as soon
as I add ORDER BY I receive the following error:

"There was a problem accessing a property or method of the OLE object."

Are ORDER BY claues not allowed for SELECT statements used as a
recordsource? As the recordsource string changes I want to be able to sort
the records differently. Any thoughts or help? Thanks.

I'm using an Access 2k ADP file connected to SQL 2k.


DJK

 

[Dan Kerkman]

I see that ORDER BY is ignored if you are using an ADP file. So, how can I
change the sort order using VB?

 

[Van T. Dinh]

Access Reports actually ignore sorting / ordering from the RecordSource,
anyway.

You need to use the Sorting and Grouping of the Report. Open the Report in
DesignView and use the Menu View / Sorting & Grouping ...

HTH
Van T. Dinh
MVP (Access)

 

[Dan Kerkman]

Yes, I know how to use that tool. However, I need to change the sort
criteria at runtime.

 

[Van T. Dinh]

Well ...If you need to change the sorting in the Report at run-time, the
Order By clause in the SQL is not going to help. Access Report will not use
the ordering returned by the SQL Select Query.

Check Access VB Help on the OrderBy & OrderByOn Properties of the Report
Object. Not sure how this works with Reports as I don't use ADP.

 

[Vadim Rapp]

DK> I"m using a SQL SELECT statement as the
DK> recordsource for a report. As long as I do not
DK> include an ORDER BY clause the SQL string works
DK> fine but as soon as I add ORDER BY I receive the
DK> following error:

DK> "There was a problem accessing a property or
DK> method of the OLE object."

Try using ORDER BY <seq number> instead of ORDER BY <field name>. For
example, instead of SELECT C1,C2,C3 ... ORDER BY C2 use ...ORDER BY 2

Vadim