Order By DateField... ASC if this / DESC if that

[MatthewGrace]

My goal is to sort by a 'ShipDate' Field in my Query from oldest to newest... unless the 'ShipmentStatus' Field is marked as Shipped, in which case I want Shipped items to sort by that same Date Field but this time by newest to oldest.



Here's a picture example if it helps:


I feel like I'm overlooking something obvious. Thanks to anyone who helps.

[June7]

AFAIK, cannot be done. A field can have only one sort parameter and not dynamic based on any criteria. Cannot build a conditional expression in Sort row of query.

You could have subreports with different sort parameters.

[orange]

You could break it into 2 queries
-Shipped
-not shipped
and sort each as you want.

[InsuranceGuy]

You need to intentionally generate Null values in calculated fields to accomplish this. I think this matches what you wanted to accomplish:

SELECT qryShipmentManager.ShipDate ,qryShipmentManager.ShipmentStatus
FROM qryShipmentManager
ORDER BY AlreadyShipped DESC
 ,IIf([ShipmentStatus] = "Shipped", NULL, 1) * [ShipDate] ,IIf([ShipmentStatus] = "Shipped", 1,NULL)* [ShipDate] DESC

Cheers,

Jeff

[June7]

And where do you put that IIf()? Can't do it in query builder as you have posted.

[InsuranceGuy]

I'm not sure I'm following your question June. I loaded his sample data into a dummy table and built this query in the Query Builder. Feel free to give it a try. I'm very open to feedback!

Jeff

[June7]

I did test but apparently had something shouldn't have because of copy/paste. Now don't get error and it does the dual sort. Still has only one sort parameter for each field as I said had to. I just don't think I would have ever figured out those calculations. Learned 2 new things today.

And I referenced table instead of the query.

[MatthewGrace]

About 20 minutes after I posted this the answer hit me like a brick... I was so excited to figure something like this out, and now I see InsuranceGuy not only beat me to it, but his answer is a little more pro looking than mine. Thanks for the help!