Max function not working (1 Viewer)

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;

Click to expand...

ridders said:

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

Click to expand...