Sorted continuous subform - inconsistent results

[easyrider]

Hi all,



I have a continuous subform (called Service_F) that shows the service history of the record that is currently shown in the parent form, Vehicle_F.

For simplicity, the Service_F has three fields: ServiceDate, Description, RepairShop.

I have the OrderBy property of the subform set to ServiceDate DESC. But the results are inconsistent. Some vehicle records will show the service history in ascending order while others show it in descending order (which is what I want - most recent on top).

Anybody have an idea where I should look to fix this?

Thanks!

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

[Micron]

Anybody have an idea where I should look to fix this?

Sorting on more than 1 field? The field is text, not date data type? You're using Format function on a date field, which coerces dates to string data type? You're using GroupBy?

[CJ_London]

I have the OrderBy property of the subform set to ServiceDate DESC.

I suspect it is not always being applied. Suggest instead use Order By in the subform recordsource

[easyrider]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

This DB does the same thing and was easier to strip out all of the confidential data and replace it with meaningless data. There are only 3 records in the main property table.

When I pull up the property record form, I can scroll through the 3 properties via the nav buttons at the bottom and the continuous subform is sorted correctly. However, when I search for a property (search button next to address line) the resulting "filtered" record continuous subform is sorted in ascending order. If I turn off the filter, the property form goes back to the first record and the subform is still in ascending order. If I close the form and reopen it, the subform is sorted in descending order.

I broke something stripping out the info, so ignore the main menu and just open the PropertyRecord_F

So, my hunch is that it has something to do with the filter... ??

SortTestDB.zip

[CJ_London]

As I suggested in post#4, use the order by in the recordsource

SELECT Service_T.* FROM Service_T ORDER BY Service_T.LastPumpDate DESC;

[easyrider]

As I suggested in post#4, use the order by in the recordsource

SELECT Service_T.* FROM Service_T ORDER BY Service_T.LastPumpDate DESC;

Sorry for being dense Ajax, but how do I do that? (brain not working today...)

nvrmnd, I figured it out and that works, Ajax. Thank you!!

Would love to know the technical reason why, but will have to save that for another day.

[CJ_London]

deleted as OP has worked it out