An IIF Statement that is killing me

[djkekos]

Fairly new to access and I've searched all over to get the syntax on this right.

I have a table with a field [Response] and a field [Index Date] along with an Id number.

I've tried this IIF statement but keep getting the Invalid Syntax error - "You may have entered an operand without an operator"

Due Date: IIF([Response]="IC Review",[Index Date]+30, IIF([Response]=”3MTH F/U”,[Index Date]+90, IIF([Response]=”6MTH F/U”,[Index Date]+180, IIF([Response]=”12MTH F/U”,[Index Date]+360))))

Any ideas?

 

[plog]

When you get that many nested IIF statements its probably time to create a custom function and pass the fields it uses to that function.

However, in your case, I'd set up another table called 'ResponseTimeFrames' with two fields. The first would hold all your [Response] values (i.e. 'IC Review', '3MTH F/U', etc.) and a field that holds how many days you need to add to [Index Date] to determine the Due Date that you are trying to calculate above. Your finished table would look like this:

[ResponseType], [TimeFrame]
"IC Review", 30
"3MTH F/U", 90
"6MTH F/U", 180
"12MTH F/U", 360

Then in your query you link [ResponseType] of this new table to [Response] of the existing table and calculate Due Date this way:

Due Date: DateAdd("d", [TimeFrame], [Index Date])

 

[MarkK]

Your last IIF() doesn't have a <falsepart>

IIF([Response] = "12MTH F/U", [Index Date] + 360)

Also, the quote characters you are using do not appear to be chr(34). Notice the difference between your keyboard ...

”3MTH F/U”

and what I get if I type that same thing...

"3MTH F/U"

Finally, consider using the Switch() function rather than nested IIF()...

DueDate: [IndexDate] + Switch([Response] = "IC Review", 30, [Response] = "3MTH F/U", 90, [Response] = "6MTH F/U", 180, [Response] = "12MTH F/U", 360)

... or better yet read plog's post.

 

[djkekos]

I got lagbolt's final reply to work for me and it went great.

Plog, I created the table you suggested and tried to run the query but it gave me this message:

"Cannot join on Memo, OLE, or Hyperlink Object ([06_tbl_Second_Third_Attempts_EXPORT.Response=tblResponseTimeFrames.ResponeType)."

I'm sure I did some wrong on it, but thankfully the other suggestion worked. Thanks for your reply.