Sorting subform records by two fields

[JohnB]

Hi.

Is it possible to have subform records appear sorted by two fields, in a
kind of sorting/grouping way (like you can do in a report)? The two fields I
want to sort by are PlacementYear and PlacementStageOrder. I want the subform
records to appear with the PlacementYear fields sorted descending and then,
within that, the PlacementStageOrder field appearing in ascending order. For
example:

PlacementYear PlacementStageOrder
2006 1
2006 2
2006 3
2005 1
2005 2
2004 1
2004 2

I've tried setting using Sort on these two fields in the query that supplies
the subform but this only seems to make the records appear in PlacementYear
decending order, even though I can run the query and see that it is doing
exactly what I want.

I then tried using Order By in the subforms properties but again, I only
seem to be able to get things sorted by one field - PlacementYear.

Is there a way of doing this? Perhaps by some On Load code for the subform?

Thanks, JohnB

 

[NetworkTrade]

if it is not too much work; whip up a new test form based on that query that
you know works...

and verify if the form looks the way you want...

if so; delete the subform from your main form and add back in this new test
form

 

[JohnB]

Thank you for this.

Your reply suggests that you believe my sorting the records in the supplying
query is the correct way to do this and that I've somehow done something
wrong in the subform to undo the sorting. If so I wonder what that could be.

Is there a way of using the Order By in the subforms properties to sort by
two fields?

Sorry to repeat my questions but your reply doesn't answer them. I'll try
your suggestion.

Thanks again, JohnB

 

[NetworkTrade]

sorry about not answering your specific question.

I like to handle the sorts in the query and keep forms neutral.

yes; you can sort multiple fields. In the 'Order By' area in the property
of the form you would put: TableName.field1,field2,field3

 

[JohnB]

No problems. Thanks for this. I'll try that too, although I'd really like to
find out why it's not working using the sort in the query. I'll have a play
around and will post back here - might be next week however.

Cheers, JohnB

 

[Elaine]

Dear John

Have you tried the solution proposed by NTC, ie, using the OrderBy property
of the form to order by two fields?
I have the same situation as you, I need to sort a form by Appointment Date
then by Appointment time. However, NTC's solution has not worked for me.
Please let me know if you found a reliable solution. I shall post it here if
I do.

Cheers,
Elaine

 

[JohnB]

Hi Elaine.

Sorry for the delay but it's taken a while for me to get around to working
on the db again.

In fact I found that I had made a stipid mistake and now realise that the
sort in the query does actually work. Also I've read in other posts that
using the Order By property of the form is not reliable because users can
change this by doing their own sort.

You probably know this but if you want to have the records presented to the
form or subform sorted by more than one field, then the sort priority is set
by the relative position if each field in the query. Fields to the left are
sorted first, then those to the right. So in your case, as long as you have
the AppointmentDate field to the left of the ApointmentTime field, the sort
should work. You can see if the query does what you want by looking at it in
datasheet view. If it does, then the form should show the records in that
order unless you have some code overriding it. Sorry if that's all obvious
to you.

Hope that helps.

 

[JohnB]

Hi again.

Thanks for the help but I realise that my sort was working after all! Just
me getting confused.

Cheers, JohnB