Access query syntax error

[leeb91]

I am having trouble running this query and it is not clear to me why it won't run. So What I am trying to do is 1. Check if [_DATA].[Claim_Status] is null or not 2. if it is null, check indemnity_paid if it's > 0 and if it is, output "SETTLED". 3. If indemnity_paid is not > 0 , then it would check resolution date and if resolution date is greater than 1/1/1920, it will output "DISMISSED" and if the resolution date is not greater than 1/1/1920 or it is null , it should output "PENDING". Above was for blank status and I need to make changes for non-blank cells too. 1. Check if it is non-blank-cell that has either "CLOSED" or "CLOSED*". if the claim_status is either of those, it will check indemnity paid and if there is greater than 0 indemnity paid, it should output "SETTLED" and if it is not >0, it should output "DISMISSED". Can anyone leave comments regarding the code below? Thanks in advance!
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Claim_Status: iif([_DATA].[Claim_Status] IS NULL,iif([_DATA].
[Indemnity_Paid]>0,"SETTLED",iif([_DATA].
[Resolution_Date]>#1/1/1920#,"DISMISSED","PENDING"),iif([_DATA].[Claim_Status]
IN ("CLOSED","CLOSED*","ADDMOREHERE"),iif([_DATA].
[Indemnity_Paid]>0,"SETTLED","DISMISSED"),[Status].[Claim_Status]))))</code>

 

[ranman256]

you shouldnt use complex IIF statements in a query.
Either,
change this to a function to return your value
or
build a table and join it in the query to return your value

 

[Micron]

Note also that if any of the values are missing, the IIF will not work as you expect based on what you have here.

 

[james_lankford]

you shouldnt use complex IIF statements in a query.
Either,
change this to a function to return your value
or
build a table and join it in the query to return your value

what's the difference between putting the IIF statement directly in your query or putting it in a function and having your query call the function ?

is there really a speed difference or what ?

 

[james_lankford]

and is this possible
IN("CLOSED","CLOSED*","ADDMOREHERE")

I didn't know you could put a wildcard in an "in" statement

 

[Norie]

IsNull is a function and should be used like this IsNull([_DATA].[Claim_Status]).

Also, you can't use wildcards in an IN clause.