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?
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
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.
