OrderBy Vs ServerFilter

  • Thread starter Thread starter Henry
  • Start date Start date
H

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
 
Henry,

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

Hope this helps

Regards
Guy
 
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...
Click to expand...

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

?
 
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
 
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
 
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."
Click to expand...

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.
 
Back
Top