Subform Problem - help needed please

[Joe]

In an Access 2003 Asset database (based on Microsoft’s template) I have the
following tables - tblAssets, tblAssetDescription, tblDepartments,
tblEmployees, and tblAssetAssignment that contains the fields
AssetAssignmentID (autonum,pk), AssetID (num,fk), DepartmentID (num,fk),
EmployeeID (num,fk), AssignmentDate (date/time) and AssignmentDescription
(txt).

frmAssetAssignment is a subform of frmAssets and records the history of the
assignment of each asset by date, department and employee. These forms are
working well but I am having difficulty getting frmEmployeesAsset which is a
subform of frmEmployees to accurately show the latest recorded asset(s) for
each employee. The record source for the form contains the following query

SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
DMax("AssignmentDate","tblAssetAssignment","[EmployeeID]=" &
Nz([EmployeeID],0)) AS Expr1, tblAssetDescription.AssetDescription,
Assets.SerialNumber
FROM tblAssetDescription LEFT JOIN (Assets LEFT JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID;

When the query is run the Expr1 field contains error# and some of the assets
are allocated to more than one person because the latest date is not being
selected by the query. I have tried changing the criteria element of the
statement to AssetID and AssignID and still get the same error message. I
can’t see where I've gone worng and would greatly appreciate any guidance.

Thanks,

Joe