Query All Fields But Sort One

[Gary Schuldt]

I have a 2-table query where all the fields (*) from each table are selected
in the QBE window. But I want the query sorted by one of the fields.

Do I have to change the field spec from * to enumerating each one so that I
can specify the sort?

Gary

 

[Allen Browne]

No. You can specify the field to sort by without having to list it in the
Field List of your query.

Example:
Me.RecordSource = "SELECT * FROM MyTable ORDER BY MyField;"

You can even get the names of all the Fields by looping through the Fields
collection.

 

[Gary Schuldt]

Thanks, Allen.

I have always constructed my Query using the Query Design window, where you
display the tables, their relationships, and then drag the desired fields
into the field columns below, where you set the criteria and sorts, etc.

Where would I place your statement

"Me.RecordSource = "SELECT * FROM MyTable ORDER BY MyField;" ?

Thanks.

Gary

 

[Allen Browne]

Place it in the event where you would like to change the sort order, e.g.
the Click of a command button.

Are you aware that you can also change the sort order by changing the
OrderBy property of the form? Try this:

1. Place a combo box on your form, and give it these properties:
Control Source {must be blank}
Row Source WhateverYourQueryIsCalledHere
Row Source Type Field List
Name cboSortField
After Update [Event Procedure]

2. Click the Build button (...) beside the AfterUpdate property.
Access opens the Code window.

3. Between the "Private Sub..." and "End Sub" lines, paste:
If IsNull(Me.cboSortField) Then
Me.OrderByOn = False
Else
Me.OrderBy = Me.cboSortField
Me.OrderByOn = True
End If

As soon as you select a field in the combo, the form is sorted by that
field.

 

[Gary Schuldt]

Thanks, Allen!

1. OK, I'll put the code in the OpenEvent for that form to effect the
default sort.

2. I'll use the OrderBy combo box technique you suggest to allow the user
to change the order.

I always wondered about the uses of the "Row Source Type Field List" for a
cbx!
Can't wait to try it!

Gary

Place it in the event where you would like to change the sort order, e.g.
the Click of a command button.

Are you aware that you can also change the sort order by changing the
OrderBy property of the form? Try this:

1. Place a combo box on your form, and give it these properties:
Control Source {must be blank}
Row Source WhateverYourQueryIsCalledHere
Row Source Type Field List
Name cboSortField
After Update [Event Procedure]

2. Click the Build button (...) beside the AfterUpdate property.
Access opens the Code window.

3. Between the "Private Sub..." and "End Sub" lines, paste:
If IsNull(Me.cboSortField) Then
Me.OrderByOn = False
Else
Me.OrderBy = Me.cboSortField
Me.OrderByOn = True
End If

As soon as you select a field in the combo, the form is sorted by that
field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen.

I have always constructed my Query using the Query Design window, where you
display the tables, their relationships, and then drag the desired fields
into the field columns below, where you set the criteria and sorts, etc.

Where would I place your statement

"Me.RecordSource = "SELECT * FROM MyTable ORDER BY MyField;" ?

Thanks.

Gary