Losing my mind. "Extra ) in query expression"

[shin_mitsugi]

Hey guys. I am losing my mind. I am getting the error "Extra ) in query expression "pending reqs")'
Can somebody help me figure this out? My mind is melting. I can't get the syntax error fixed. This if statement is huge. Not even sure I got it all coded correctly. I've pasted both the IF statement logic and the IIF script below. I'm sorry for the trouble Thank you.

if(Atty Signed <> “”, “Pending Filing”
if(Complt Ver Recd <> “”, “Pending Signature”
if(Complt Ver Sent <> “”, “Pending Client Verification”
if(Complt Drafted <> “”, “Pending Send to Client”
if(Exam Complete <> “”, “Pending Draft”
if(AND(OR(BrchLtr RecCorr <> “”,Brch Ltr Waived <> “”),OR(Orig Note Recd <> “”,LNA Recd <> “”),AND(Chain of Title Correct <> “”,Chain of Title = “Correct”, OR(Chain of Title Incorrect = “”,Chain of Title Incorrect < Chain of Title Correct)), Title Rpt Recd <> “”), “Pending Exam”, “Pending Reqs”

IIF([TITLE.AttySigned] is not null,"Pending Filing",
IIF([TITLE.Compltverrecd] is not null,"Pending Signature",
IIF([TITLE.Compltversent] is not null,"Pending Client Verification",
IIF([TITLE.CompltDrafted]is not null,"Pending Send to Client",
IIF([TITLE.ExamComplete],"Pending Draft",
IIF(([TITLE.brchltrreccorr]is not null OR [TITLE.brchltrwaived] is not null) AND 
([ODOC.OrigNoteRecd] is not null OR [ODOC.LNARecd] is not null) AND 
([ODOC.ChainofTitleCorrect] is not null AND [ODOC.ChainofTitle]="Correct" AND ([ODOC.ChainofTitleIncorrect] is null OR [ODOC.ChainofTitleIncorrect] < [ODOC.ChainofTitleCorrect])) AND
[TITLE.TitleRptRecd] is not null)))))),"Pending Exam","Pending Reqs") as PendingComplaint

[CJ_London]

best thing to do is to copy to word then colour match pairs of brackets

Other trick is to build in small components and test at each stage

IIF([TITLE.AttySigned] is not null,"Pending Filing","1")

IIF([TITLE.AttySigned] is not null,"Pending Filing",IIF([TITLE.Compltverrecd] is not null,"Pending Signature","1"))

also consider using the switch function to replace some or all of the nested iifs which can make things easier to read

switch([TITLE.AttySigned] is not null,"Pending Filing",[TITLE.Compltverrecd] is not null,"Pending Signature",[TITLE.Compltversent] is not null,"Pending Client Verification"..etc..)

[shin_mitsugi]

Thank you for the suggestion. The switch function seems like a powerful tool. I apologize for my ineptitude but I tried to convert the formula into switch and failed. Access states that I have the wrong number of arguments in the formula. I can try to break it apart below. I'm 100% sure I could write this in Excel but I'm just not familiar enough with Access and am still learning.

switch([TITLE.AttySigned] is not null,"Pending Filing",
[TITLE.Compltverrecd] is not null,"Pending Signature",
[TITLE.Compltversent] is not null,"Pending Client Verification",
[TITLE.CompltDrafted]is not null,"Pending Send to Client",
[TITLE.ExamComplete],"Pending Draft",
(([TITLE.brchltrreccorr]is not null OR [TITLE.brchltrwaived] is not null) AND 
([ODOC.OrigNoteRecd] is not null OR [ODOC.LNARecd] is not null) AND 
([ODOC.ChainofTitleCorrect] is not null AND [ODOC.ChainofTitle]="Correct" AND ([ODOC.ChainofTitleIncorrect] is null OR [ODOC.ChainofTitleIncorrect] < [ODOC.ChainofTitleCorrect])) AND [TITLE.TitleRptRecd] is not null),"Pending Exam","Pending Reqs") as PendingComplaint

[JoeM]

My advice:

Any time you have a "huge" function, change it to a User Defined Function in VBA. They are much easier to read and manage.
Many complex IIF statements can be replaced with Case statements, which don't require all those parentheses.
See: http://www.techonthenet.com/access/f...anced/case.php

[shin_mitsugi]

Can I write Case statements directly into the Access SQL window? My thought was that it didn't allow the Case statement. How would I write that up as a calculated column? I just tried the following and it told me I used a reserved word "SELECT"

SELECT CASE now() - [TITLE.brchltrreccorr]
Case <10 
[Mil Search Aging] = "Not Aged"
Case >10 and <20
[Mil Search Aging] = "Aged"
END SELECT

[CJ_London]

switch is the nearest equivalent of case is access sql

up to here - your code looks fine (I've added the final bracket

switch([TITLE.AttySigned] is not null,"Pending Filing",
[TITLE.Compltverrecd] is not null,"Pending Signature",
[TITLE.Compltversent] is not null,"Pending Client Verification",
[TITLE.CompltDrafted]is not null,"Pending Send to Client",
[TITLE.ExamComplete],"Pending Draft")

so build this bit up bit by bit, highlighting the bracket pair as I've done here. I'm not familiar with your logic so check each bracketed element is as required

(([TITLE.brchltrreccorr]is not null OR [TITLE.brchltrwaived] is not null) AND
([ODOC.OrigNoteRecd] is not null OR [ODOC.LNARecd] is not null) AND
([ODOC.ChainofTitleCorrect] is not null AND [ODOC.ChainofTitle]="Correct" AND ([ODOC.ChainofTitleIncorrect] is null OR [ODOC.ChainofTitleIncorrect] < [ODOC.ChainofTitleCorrect])) AND [TITLE.TitleRptRecd] is not null),"Pending Exam","Pending Reqs")

You can now see that the you have a condition and a result - Pending exam, but no condition for Pending Reqs - presumably because you want an iif and switch does not have an 'else' option as such. But there is a workaround

Try this (untested) code - last line shown, rest of the code as you have it. Note you don't actually need the green brackets

....is not null),"Pending Exam",1=1,"Pending Reqs")

If the code has got this far then 1=1=true so Pending Reqs should be returned.

[JoeM]

Are you writing this for Access or for SQL?

If writing it in Access, make a User Defined Function in Access VBA. You can then use that UDF in your Access queries.

If writing it in SQL, there actually are CASE statements in SQL, they are just formed a little differently.
See these links for descriptions and examples:
http://www.techonthenet.com/sql_serv...tions/case.php
https://msdn.microsoft.com/en-us/library/ms181765.aspx
https://sqlschool.modeanalytics.com/intermediate/case/