Sorting Sub form By Descending Date Error (1 Viewer)

[access2010]

We have tried many different combinations to try and sort the date in our Access 2003 sub form without success.

Could we please be advised as to what we have done wrong?

Thank you

Nicole

 

[theDBguy]

Try removing whatever is in the Order By property of the subform.

 

[Pat Hartman]

The RecordSource query should always contain an order by. Sometimes you might want to support additional sort options or if you use a DS view subform, you have lots of built in options. Just make sure you have the form's properties set to not sort on open so you don't override your query.

 

[access2010]

Try removing whatever is in the Order By property of the subform.

Thank you for your suggestion, theDBguy, which we have tried, but we still cannot get the;

TransactionDate yo sort in a descending order
Followed by TransactionNumb in a descending orde

Would you have another suggestion to try?

Crystal

 

[access2010]

The RecordSource query should always contain an order by. Sometimes you might want to support additional sort options or if you use a DS view subform, you have lots of built in options. Just make sure you have the form's properties set to not sort on open so you don't override your query.

Thank you Pat Hartman for your suggestion.
We have tried sorting in both of the queries, without any success.
Would you have an other suggestion to try?

Thank you.
Nicole

 

[The_Doc_Man]

In the DB you posted, the TransactionDate field is a date/time and the TransactionNumb field is text (digits) string with explicit leading zeros. You have one query that sorts the date field first (descending), then the number field. Here is the question: What order did you really want, and when you open the query with the ORDER BY clause, does it present the records in the order you want? If not, what order DOES it produce?

By the way, it would have been nice to identify which form is the one that misbehaves so that we don't have to guess.

 

[arnelgp]

is this what you meant.

 

[gemma-the-husky]

You have the sort order defined in the subform (data tab), and that will take precedence over the sort order declared in the query.
Delete that sort order, and then the sort order will comply with the query.

However, you have to be careful, as when you close and save the form, you might save a new sort order with the form, and then you have the problem again. I also don't think you can manually set the sort order as Column A descending, Column B ascending (as you can do in the query)

If you release the database as an accde, then any temporary sort order won't be saved, as users can't save changes like that.
You might add a button to re-assert the default sort order, by deleting the current sort order.

I added a button at the bottom called btndefault and tried the code below. This works, but although it temporarily removes the sort order, it's not saving the subform with a blank sort order. I'm not sure why, and it would be a pain to have to keep doing this.

I'm actually struggling to find the right code to remove the sort order set in the form (subform) and save the change without a sort order.

Private Sub btnDefault_Click()
Me.OrderBy = ""
Me.Requery
End Sub

,

 

[access2010]

is this what you meant.

Thank you, arnelgp.

Your suggestion is P.E.R.F.E.C.T.

Thank you.
Fabiola