This statement is returning 2 as the value.
(select MAX(PayId) from EmployeePaymentDetails where PayDate = ( select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID) )
That's evaluating as SELECT MAX(PayId) WHERE PayDate = '19/06/2010'. So your statement overall is evaluating as
SELECT * from EmployeePaymentDetails where EmpId = 1 and PayID = 2
...which of course returns no records.
Since PayID isn't unique, this logic doesn't really offer you anything. How about the below..?
SELECT
*
FROM
EmployeePaymentDetails
WHERE
(((EmployeePaymentDetails.EmpID)=[@EmpID])
AND
((EmployeePaymentDetails.PayDate) =
(
select Max(PayDate) from EmployeePaymentDetails where EmpID=@EmpID))
);