Home>

Please tell me about access. I put in a function that extracts the latest date in a query, but I get a syntax error.
DMax ("Date", "Employee Master", "Employee Number ="&[Employee Master]. [Employee Number])
I put this formula in the field called Date.
However, I get the message "Syntax error in query expression'employee number =': no ​​operator". I hope you can point out what is wrong.
Thank you.

Employee number of employee master: Numerical type
Date: Date/time type

SQL

SELECT Employee Master. Employee Number, Employee Master. Name, Employee Master. Date of Joining, Employee Master. Date of Birth, Employee Master. Zip Code, Employee Master. Prefecture, Employee Master. City, Ward, Town, Employee Master. Address. , Employee Master. [Apartment, etc.], Employee Master. Retired, ([Employee Master]. [Prefecture]&([Employee Master]. [City])&[Employee Master]. [Address]&([Employee Master] Master]. [Apartment, etc.])) AS All Addresses, DateDiff ("yyyy", [Date of Birth], Date ()) + (Format ([Date of Birth], "mmdd")>Format (Date (), "mmdd"))&"years" AS age, employee master. Date
FROM Employee Master LEFT JOIN Employee Master ON Employee Master. Employee Number = Employee Master. Employee Number
WHERE (((Employee Master. Retirement) = False) And ((Employee Master. Date) = DMax ("Date", "Employee Master", "Employee Number ="&Employee Master. Employee Number))) ;
  • Answer # 1

    Is [Date] for history management?
    Anyway, isn't the LEFT JOIN clause unnecessary?

    SELECT Employee Master. Employee Number, Employee Master. Name, Employee Master. Date of Joining, Employee Master. Date of Birth, Employee Master. Zip Code, Employee Master. Prefecture, Employee Master. City, Ward, Town, Employee Master. Address. , Employee Master. [Apartment, etc.], Employee Master. Retired, ([Employee Master]. [Prefecture]&([Employee Master]. [City])&[Employee Master]. [Address]&([Employee Master] Master]. [Apartment, etc.])) AS All Addresses, DateDiff ("yyyy", [Date of Birth], Date ()) + (Format ([Date of Birth], "mmdd")>Format (Date (), "mmdd"))&"years" AS age, employee master. Date
    FROM Employee Master
    WHERE (((Employee Master. Retirement) = False) And ((Employee Master. Date) = DMax ("Date", "Employee Master", "Employee Number ="&Employee Master. Employee Number))) ;