Order by column alias problem

[Kapten]

Hi,

I changed from MS SQL-Server to MS Access in an application and a SQL-select statement stoped to work.

Here is the code:
"SELECT TblContact.*,(select top 1 cs.sendTime FROM TblContactSent cs WHERE TblContact.contactId = cs.contactId ORDER BY cs.sendTime DESC) as ST from tblContact ORDER BY ST DESC"

I know Access can't use alias in the order by clause so I changed that to ORDER BY 5 DESC. No error but it doesn't sort by sendTime.

I would highly appreciate any help. I've been looking for a solution for days. Either that it can't be done or (preferable) how to solve it.

Thanx in advance!

Kapten

 

[Galaxiom]

The subquery omits the As from the aliasing:

select top 1 cs.sendTime FROM TblContactSent [COLOR=red][B]AS[/B][/COLOR] [COLOR=black]cs[/COLOR] WHERE TblContact.contactId = cs.contactId ORDER BY cs.sendTime DESC

No it is more than this. The Where clause is totally messed up too.
TblContact.contactID is not even in the source tables of the subquery.
I can't work out what you are trying to do.

 

[Kapten]

Hi GalaxiomAtHome,

thanx for your reply. I inserted AS in the query but that didn't make any difference. This query works in MS-SQL-Server. I'm trying to get the latest sendTime from TblContactSent (if there are any) . This table can have many rows for each contactId but I only want the latest and I want to sort the recordset by sendTime with the latest at top and the null rows at the bottom.

Hope I was clear enough.