CODEWITHSUNDEEP
sqlms-access
Derek
Derek

Reputation: 485

Need to sort by Expression in Access Query

I have an expression that is returing the exact data I need, however Access won't allow me to sort by it...which renders the data almost useless.

Expression in Query Designer:

DelDays: Workdays(Format([Shipment Date]+1,'0000-00-00'),Format([POD Delivery Date],'0000-00-00'))

Here is the SQL query:

SELECT RTGFDXGround.[Invoice Number], RTGFDXGround.[Express or Ground Tracking ID], RTGFDXGround.[Service Type], RTGFDXGround.[Shipment Date], RTGFDXGround.[POD Delivery Date], RTGFDXGround.[Net Charge Amount], RTGFDXGround.[Zone Code], Workdays(Format([Shipment Date]+1,'0000-00-00'),Format([POD Delivery Date],'0000-00-00')) AS DelDays, Mid([Recipient Zip Code],1,5) AS ToZip, RTGFDXGround.[Shipper Zip Code]
FROM RTGFDXGround
WHERE (((RTGFDXGround.[Invoice Number])="6788") AND ((RTGFDXGround.[Ground Tracking ID Prefix])<>"715"))
ORDER BY RTGFDXGround.[Zone Code] DESC , Mid([Recipient Zip Code],1,5) DESC;

Any suggestions on how I can get this to sort by 'DelDays' DESC? I'd prefer to have the logic work in Queries without the need to use a Report.

Thanks as always!

Upvotes: 0

Views: 1011

Answers (1)

Hogan
Hogan

Reputation: 70523

With SQL you can just put stuff in a sub-query and use the results.

SELECT *
FROM
(
  SELECT RTGFDXGround.[Invoice Number], RTGFDXGround.[Express or Ground Tracking ID], RTGFDXGround.[Service Type], RTGFDXGround.[Shipment Date], RTGFDXGround.[POD Delivery Date], RTGFDXGround.[Net Charge Amount], RTGFDXGround.[Zone Code], Workdays(Format([Shipment Date]+1,'0000-00-00'),Format([POD Delivery Date],'0000-00-00')) AS DelDays, Mid([Recipient Zip Code],1,5) AS ToZip, RTGFDXGround.[Shipper Zip Code]
  FROM RTGFDXGround
  WHERE (((RTGFDXGround.[Invoice Number])="6788") AND ((RTGFDXGround.[Ground Tracking ID Prefix])<>"715"))
)
ORDER BY DelDays DESC

Upvotes: 1

Related Questions