MS-Access : Syntax error (missing operator) in query expression

Question

I keep getting a syntax error (missing operator) in query expression for this formula in MS-Access.

IIf([Employee List].[Employee Type]=”Employee”,Format([Employee List].[Date of Birth],'dd/mm/yyyy'),’01/01/1910’) AS Date of Birth

Basically for employee type employee show their date of birth in the employee list otherwise show 01/01/1910 for everyone else.

Does anyone know why and how I can fix this?

Answer 1

I think your quotes are wrong. MS Access uses double quotes for strings:

IIf([Employee List].[Employee Type] = "Employee",
    Format([Employee List].[Date of Birth], "dd/mm/yyyy"),
    "01/01/1910"
   ) AS DateofBirth

Also, the column alias needs to be a single word or be escaped.

Answer 2

Do you really use 3 different kinds of quotes: ” and ' and ’?
Use either " or '.
Also the alias Date of Birth should be enclosed in square brackets because it contains spaces, but since it exists already as a column in the table it will produce a circular reference error so change it to something like:

IIf(
  [Employee List].[Employee Type] = 'Employee',
  Format([Employee List].[Date of Birth], 'dd/mm/yyyy'),
  '01/01/1910'
) AS Date_of_Birth