Help with query and order by clause

[eerich]

Hello,

I have a query that I wrote to calculate the aging in days of a person's last logon to determine whether they are within Co policy. I would like to order by the aging date descending, but receive errors with every combination I've tried. My query is:

SELECT UserName, UserID, Status, LastLogon, DateDiff('d',Now(),LastLogon)*-1 AS DaysAging
FROM
(SELECT UserName, UserID, Status, format$(mid(lastlogindate,3,2) & "-" & right(lastlogindate,2) & "-" & left(lastlogindate,2),'mm/dd/yyyy') AS LastLogon FROM as400users WHERE Status="*Enabled")
Order by DateDiff('d',Now(),LastLogon)*-1 desc;


-----------------


I've also tried replacing the calculation in the order by with the alias name 'DaysAging' but that doesn't work either.


Finally is there a way to prevent the Access SQL code from adding the following once the query has been saved?
...Status="*Enabled"]. AS [%$##@_Alias];...


Any help is much appreciated.

 

[PHV]

SELECT UserName, UserID, Status, LastLogon, DateDiff('d',LastLogon,Now()) AS DaysAging
FROM
(SELECT UserName, UserID, Status, format$(mid(lastlogindate,3,2) & "-" & right(lastlogindate,2) & "-" & left(lastlogindate,2),'mm/dd/yyyy') AS LastLogon FROM as400users WHERE Status="*Enabled") AS X
ORDER BY 5 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

Hi PHV,

I received a 'datatype mismatch in criteria expression' error when I applied the solution. I'm not certain as to what is causing it. My revised query based on your help was:



Select UserName, UserID, Status, LastLogon, DateDiff('d', Now( ), LastLogon) * -1 AS DaysAging
FROM
(Select UserName, UserID, Status, format$(mid(lastlogindate,3,2) & "-" & right(lastlogindate,2) & "-" & left(lastlogindate,2),'mm/dd/yyyy') AS LastLogon
FROM AS400Users
Where Status = "*Enabled") AS X
Order by 5 DESC;