OrderBy Vs ServerFilter

[Henry]

How can I sort the order of rows dsiplayed in a form based on a view.

The help tells me that OrderBy cannot be used with ADPs and ServerFilter
should be used however this appears to only used for specifying a Where
caluse to limit the rows returned and not to order them !

Thanks in advance

Henry

 

[Guy Horton]

Henry,

Add a Top and Order By clause to your View Select statement.

Hope this helps

Regards
Guy

 

[Lyle Fairfield]

Cannot be done...

See SQL BOL

There are, however, a few restrictions on the SELECT clauses in a view
definition. A CREATE VIEW statement cannot:
Include ORDER BY, COMPUTE, or COMPUTE BY clauses...

Have you considered using a stored procedure (which accesses the view) as
the recordsource for the form as in:

SELECT * From MyView ORDER BY fldOperator, fldDate

?

 

[Henry]

This is the way I've had to go but it appears the SP does not integrate with
a main and sub form scenario as well as a view does and code is needed to
keep the two in sync.

I am at a loss to understand why in the help file it states that Order By
cannot be used with an ADP and one should use ServerFilter instead however
ServerFilter simply uses the Where clause and not Order By...

Thanks

Henry

 

[Guy Horton]

What do you mean it can not be done?

Try this against the NorthwindCS database.

CREATE VIEW dbo.qselCustomersByCompanyName
AS
SELECT TOP 100 PERCENT CustomerID, CompanyName, ContactName,
ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
FROM dbo.Customers
ORDER BY CompanyName

Forget about using the Order By property of a form that is used only to save
a default form order which can be changed by the user using the builtin
toolbar sort function.

Hope this helps
Guy

 

[Lyle Fairfield]

In the Access help file under Order By.
"Note In a Microsoft Access project (.adp), this property is ignored.
To set the sort order, you must use the ServerFilter property."

Thank you. I see it now in AC2K help. But is it valid? I fired up a
machine which had AC2K installed and tested. It allowed me to set the
OrderBy and OrderOn Properties of an ADP Form, both interactively and
through VBA, and the Form sorted as required.

Through the years, I have found that MS help is often wrong.