OrderBy (form) and ORDERBY (sql)

[Gurtz]

Hi. I am using a form's 'SortBy' property to sort pages of
records. This form is brought up when a user clicks a row
in a listbox. The form displays up to 10 records
corresponding to those in the listbox, centered around the
user's selection.

The trouble is I want the form to sort the results the
same way they are sorted in the listbox. However, I allow
the user to change the order of the results in the
listbox .. I accomplished this through a query in VB. But
since the listbox is sorted through the ORDERBY query
option, and the form is sorted through it's OrderBy
property, they differ in how they sort records where the
ORDERBY's field is the same.

For instance, if I'm ordering by "Name" and several
records each have "Steve Gurtz" as "Name," then those
records are ordered in some arbitrary way amongst
themselves. How is this done? How can I get the form and
the query to do this the same way, or is this just a
foolish way to approach the problem?

Thanks!
Gurtz
[email = no $]

 

[Dirk Goldgar]

Hi. I am using a form's 'SortBy' property to sort pages of
records. This form is brought up when a user clicks a row
in a listbox. The form displays up to 10 records
corresponding to those in the listbox, centered around the
user's selection.

The trouble is I want the form to sort the results the
same way they are sorted in the listbox. However, I allow
the user to change the order of the results in the
listbox .. I accomplished this through a query in VB. But
since the listbox is sorted through the ORDERBY query
option, and the form is sorted through it's OrderBy
property, they differ in how they sort records where the
ORDERBY's field is the same.

For instance, if I'm ordering by "Name" and several
records each have "Steve Gurtz" as "Name," then those
records are ordered in some arbitrary way amongst
themselves. How is this done? How can I get the form and
the query to do this the same way, or is this just a
foolish way to approach the problem?

Thanks!
Gurtz
[email = no $]

I think you're saying that, when you use the form's OrderBy property to
sort on a particular field, records with duplicate values in that field
aren't necessarily displayed in the same order as the records returned
by a query that uses the ORDER BY clause to sort the records on that
field. I'm a little surprised to read that, but not astonished because
in fact the order of the records with matching keys is by definition
arbitrary, and Access will return them in whatever sequence is most
convenient to it as it extracts and sorts them. As long as the returned
records are in fact in sequence on the specified sort key(s), the
results are correct.

In principle, the only way to ensure a particular sort order for records
in a relational database is to apply an ORDER BY clause including all
the fields you want to sort on. So in this case, you'd need to have
both the form and the list box's rowsource query specify more than one
sort field -- as many sort fields as necessary to get the records in a
reliable and consisten sort order in both the form and the list box.

 

[Gurtz]

Hmm.. how do I do mutliple sortbys then (in sql)? Not to
mention in the SortBy property of a form. Can I do "TagNum
and DevName" in the form's SortBy, and ".. SORT BY TagNum
AND DevName" in the query?

Thanks for your help, Dirk!
Gurtz

-----Original Message-----

Hi. I am using a form's 'SortBy' property to sort pages of
records. This form is brought up when a user clicks a row
in a listbox. The form displays up to 10 records
corresponding to those in the listbox, centered around the
user's selection.

The trouble is I want the form to sort the results the
same way they are sorted in the listbox. However, I allow
the user to change the order of the results in the
listbox .. I accomplished this through a query in VB. But
since the listbox is sorted through the ORDERBY query
option, and the form is sorted through it's OrderBy
property, they differ in how they sort records where the
ORDERBY's field is the same.

For instance, if I'm ordering by "Name" and several
records each have "Steve Gurtz" as "Name," then those
records are ordered in some arbitrary way amongst
themselves. How is this done? How can I get the form and
the query to do this the same way, or is this just a
foolish way to approach the problem?

Thanks!
Gurtz
[email = no $]

I think you're saying that, when you use the form's OrderBy property to
sort on a particular field, records with duplicate values in that field
aren't necessarily displayed in the same order as the records returned
by a query that uses the ORDER BY clause to sort the records on that
field. I'm a little surprised to read that, but not astonished because
in fact the order of the records with matching keys is by definition
arbitrary, and Access will return them in whatever sequence is most
convenient to it as it extracts and sorts them. As long as the returned
records are in fact in sequence on the specified sort key (s), the
results are correct.

In principle, the only way to ensure a particular sort order for records
in a relational database is to apply an ORDER BY clause including all
the fields you want to sort on. So in this case, you'd need to have
both the form and the list box's rowsource query specify more than one
sort field -- as many sort fields as necessary to get the records in a
reliable and consisten sort order in both the form and the list box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[John Spencer (MVP)]

Close just separate the columns by a comma. As in
TagNum, DevName

Or in SQL statement
Sort By TagNum, DevName

Hmm.. how do I do mutliple sortbys then (in sql)? Not to
mention in the SortBy property of a form. Can I do "TagNum
and DevName" in the form's SortBy, and ".. SORT BY TagNum
AND DevName" in the query?

Thanks for your help, Dirk!
Gurtz

-----Original Message-----

Hi. I am using a form's 'SortBy' property to sort pages of
records. This form is brought up when a user clicks a row
in a listbox. The form displays up to 10 records
corresponding to those in the listbox, centered around the
user's selection.

The trouble is I want the form to sort the results the
same way they are sorted in the listbox. However, I allow
the user to change the order of the results in the
listbox .. I accomplished this through a query in VB. But
since the listbox is sorted through the ORDERBY query
option, and the form is sorted through it's OrderBy
property, they differ in how they sort records where the
ORDERBY's field is the same.

For instance, if I'm ordering by "Name" and several
records each have "Steve Gurtz" as "Name," then those
records are ordered in some arbitrary way amongst
themselves. How is this done? How can I get the form and
the query to do this the same way, or is this just a
foolish way to approach the problem?

Thanks!
Gurtz
[email = no $]

I think you're saying that, when you use the form's OrderBy property to
sort on a particular field, records with duplicate values in that field
aren't necessarily displayed in the same order as the records returned
by a query that uses the ORDER BY clause to sort the records on that
field. I'm a little surprised to read that, but not astonished because
in fact the order of the records with matching keys is by definition
arbitrary, and Access will return them in whatever sequence is most
convenient to it as it extracts and sorts them. As long as the returned
records are in fact in sequence on the specified sort key (s), the
results are correct.

In principle, the only way to ensure a particular sort order for records
in a relational database is to apply an ORDER BY clause including all
the fields you want to sort on. So in this case, you'd need to have
both the form and the list box's rowsource query specify more than one
sort field -- as many sort fields as necessary to get the records in a
reliable and consisten sort order in both the form and the list box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Hmm.. how do I do mutliple sortbys then (in sql)? Not to
mention in the SortBy property of a form. Can I do "TagNum
and DevName" in the form's SortBy, and ".. SORT BY TagNum
AND DevName" in the query?

As I think you know, it's OrderBy and ORDER BY, not SortBy and SORT BY.
Don't use "and" to combine them, just separate the two field names with
a comma:

Forms!Form1.OrderBy = "TagNum, DevName"

SELECT * FROM MyTable ORDER BY Tagnum, DevName;