Advanced Sorting of Fields - Is it possible?

[abqhusker]

Is it possible to sort by more than one field, similar to how you can
sort in excel without having to move the primary sort field to the left
and the secondary sort fields to the right of that? Something like sort
by last name, then by title, then by date but the fields are in title,
last name, date order? I know I can make a query and rearrange the
fields but I want the fields in a certain order and the sorting to work
with that order, not rearrange the fields so the sort can work.
Appreciate any help you can give. I only ask here because I can't find
the answer anywhere else, and you guys are really smart on this stuff.

Ed

 

[Guest]

Yep! In the query design view add the fields but uncheck the display box.
You can also sort by the right-most characters of a field by adding a field
like this --
X: Right([MyTextField],7)
OR
X: Right([MyTextField],7) & Left("000" & [MyNumberField],4)

You can add the leading zeros for sorting.

 

[Graham Mandeno]

Hi Ed

No problem at all. In a query, simply add the fields you want to view in
the desired order, then add the fields you want to sort on to the right-hand
side and clear the "Show" checkbox for each field. Then specify Ascending
of Descending as the sort order for each. You can even sort on fields that
are not visible in your query.

However, it's usually better to use a form. You can change the sort order
in a form on the fly by changing the form's OrderBy property. For example,
you might have an option group on your form labelled "Sort by" with a number
of radio buttons labelled "Last name, Title, Date", and "Date, First name,
Last name", etc.

In the option group's AfterUpdate event procedure, you set the OrderBy
property depending on which button has been pressed:

Private Sub opgSortBy_AfterUpdate()
Select Case opgSortBy
Case 1
Me.OrderBy = "[LastName],[Title],[DateField]"
Case 2
Me.OrderBy = "[DateField],[FirstName],[LastName]"
... etc
End Select
Me.OrderByOn = True
End Sub

 

[abqhusker]

Thanks, guys! I knew you wouldn't let me down. I forgot that I could
add the fields again and then not display them, I should have known
that. But the other information you guys provided on the right most
characters and especially the form sort order properties...well...that
was awesome! I can't tell you how much I just learned from your replies
just now. It was like a light bulb just went on in my head! You guys
are the best. Thanks again...and have a Merry Christmas!

Ed