Max function not working

[aliikhlaq2006]

I need a result which displays the MAX date but its not working

PARAMETERS [Enter Month And Year (mm/yyyy)] Text ( 255 );
SELECT Advance.EmployeeID, Employees.Name, Advance.PayBack, (SELECT Sum(taken-payback) FROM advance SUB where employeeid=advance.employeeid and [Trdate]<=advance.Trdate) AS Balance, (SELECT Max(Trdate) FROM advance SUB where trdate=advance.Trdate) AS Datesw
FROM Employees INNER JOIN Advance ON Employees.ID = Advance.EmployeeID
GROUP BY Advance.EmployeeID, Employees.Name, Advance.PayBack, Format(Month([Trdate]),"00") & "/" & Year([Trdate]), Advance.Trdate
HAVING (((Format(Month([Trdate]),"00") & "/" & Year([Trdate]))=[Enter Month And Year (mm/yyyy)]))
ORDER BY Advance.EmployeeID, Advance.Trdate;

I also try to run without the subquery "Datesw" and use field but not works for me

 

[plog]

Define 'not working'. Error message? Unexpected results? No results? Causing blindness or incontinence? Ripping the local space/time continuum?

 

[isladogs]

So what happens when you run it with and without the subquery.
Do you get an error or does it give the wrong results?

One suggestion - use date format yyyymm (or yyyymmdd if date field includes day of month) as this will correctly sort latest date value

 

[Mark_]

Have you tried it without the SUB where trdate=advance.trdate ?

 

[aliikhlaq2006]

So what happens when you run it with and without the subquery.
Do you get an error or does it give the wrong results?

One suggestion - use date format yyyymm (or yyyymmdd if date field includes day of month) as this will correctly sort latest date value

There is no error it gives all result not select the MAX date in both cases

 

[The_Doc_Man]

I am not familiar with the intent of the word SUB appearing in the sub-query preceding the WHERE clause. What is the intent of that item (which IS a keyword in Access and therefore can cause headaches)?