Dynamic Sorting... (1 Viewer)

[DALeffler]

Say I have 3 fields in my table: FstName, MidName, LstName.

I want to be able to dynamically change the sort order of a query to different sort orders using the same fields via a form.

I may want to see the records sorted by LstName, MidName, Fstname; and another time see the list sorted by Fstname, Lstname, MidName; and other times by any combinations possible using the 3 fields.

I see 3 options: filter by form; programatically change the SQL Order By clause; or concatenating the table fields together in the desired order via a custom function and sorting by the function result.

Is there any difference in query/form performance between the 3 options?

 

[boblarson]

On a form you can use the OrderBy by setting:

Me.OrderBy = "MyField1, MyField2 Desc"
Me.OrderByOn = True

 

[DALeffler]

Isn't that the same as changing the Order By clause of the original SQL statement?

Is it "better" to change the SQL via the Order By clause/property or to leave the SQL the same and having the function call in the SQL perform the necessary sort order calculations?

 

[boblarson]

Unless we're talking many thousands of records, or the backend is in something like SQL Server, I don't think you would see any real discernible difference. It is going to be much more efficient to set up to just use the order by on the form. Jet is going to take control of it and requery the data anyway, regardless of which way you do it (unless, as noted you would be working with a backend where you can do server-side processing).

At least that's my view on it. I used to write SQL statements and use select case statements to determine which SQL statement to apply to the form and then requery. In my view that was a whole lot of work for basically nothing. When I learned that I could set the sort order like I showed, it simplified my code, and made things way easier and with no noticeable difference.