IIF expression missing the bracket

Question

Im using the iff expression in the query. trying to run it but it says this missing a closing parenthesis, bracket. but i checked in the expression. not sure about which one is missing. thanks!

=IIf([Project status]="Project request", "1", IIf([Project status]="Project charter", "2", IIf([Project status]="Started project", "3", IIf([Project status]="Technically closed", "4", IIf([Project status]="Closed", "5", IIf([Project status]="Cancelled", "6", IIf([Project status]="Rejected ", "7","null")))

Hi @JasonLepack i dont really wanna count them just add one more column translate them into numbers thanks — Tina Qiaotian Yan, Aug 04 '14 at 07:59
You do want to count them. If the number of ( does not equal the number of ) then your formula is written incorrectly. In this case you have seven ( at the start of every iif, so you need seven ) at the end. — LeppyR64, Aug 04 '14 at 11:15
Might be worth installing Notepad++ http://notepad-plus-plus.org/. Put the expression into Notepad++, could see the corresponding open bracket in red. Just added more closed brackets till got same expression PaulFrancis has in his answer. The SWITCH function might be alternative approach for this challenge. — SQLBobScot, Aug 04 '14 at 14:06

score 1 · Accepted Answer · answered Aug 04 '14 at 08:33

Try this IIF,

=IIf([Project status] = "Project request", "1", IIf([Project status] = "Project charter", "2", IIf([Project status] = "Started project", "3", IIf([Project status] = "Technically closed", "4", IIf([Project status]="Closed", "5", IIf([Project status] = "Cancelled", "6", IIf([Project status]= "Rejected ", "7", "null")))))))

You could solve your problem much simply by creating a new table.

tbl_projStatus
==============
projStatus          |   projectCode
--------------------+---------------
Project request     |   1
Project charter     |   2
Started project     |   3
Technically closed  |   4
Closed              |   5
Cancelled           |   6
Rejected            |   7

Then you could use a DLookup, like

= DLookup("projectCode", "tbl_projStatus", "projStatus = " & [Project status])

IIF expression missing the bracket

1 Answers1