MS Access Update Query with an IIF and DATEADD functions

Question

I have 6 fields in my "table1".

• Current Date
• Next Adjustment Date
• Months Elapsed
• Index 0
• Index 1
• Index 2.

I'm trying to create an update query for Index 1 where if the (Current Date+Months Elapsed) < Next Adjustment Date then Index 1 is Index 0 if not I want Index 1 to stay at whatever value it currently has.

I tried this

iif(DateAdd("m",[Table1]![Months Elapsed],[Table1]![Current Date])<[Table1]![Next Adjustment Date], [Table1]![Index 0]

As soon as I put in the [Table1]![Next Adjustment Date] in the iif formula I receive "The expression you entered contains invalid syntax" error.

Answer 1

You don't have the false part of the IIf specified. Try this;

IIf(DateAdd("m",[Table1]![Months Elapsed],[Table1]![Current Date])< [Table1]![Next Adjustment Date], [Table1]![Index 0], [Table1]![Index 1])

However I'm concerned that you have apparently fields stored for Current Date and Months Elapsed. Aren't these both calculated on the fly? They should be.

Answer 2

iif( is not matched by a closing )

Work out the syntax in a SELECT query. Then use that working field expression in your UPDATE. Here is my suggestion ...

SELECT
    IIf
        (
            DateAdd('m', t1.[Months Elapsed], t1.[Current Date]) < t1.[Next Adjustment Date],
            t1.[Index 0]
        )
FROM Table1 AS t1;

Note your IIf lacked the third argument, which is the value to return when the condition (the first argument) is not True. The database engine will not complain and will return Null in that situation. See also this answer.

However your description suggests you may actually want this ...

    IIf
        (
            DateAdd('m', t1.[Months Elapsed], t1.[Current Date]) < t1.[Next Adjustment Date],
            t1.[Index 0],
            t1.[Index 1]
        )

... but for an UPDATE, it makes more sense to me to move that condition to the WHERE clause and update only those rows ...

UPDATE Table1 AS t1
SET t1.[Index 1] = t1.[Index 0]
WHERE DateAdd('m', t1.[Months Elapsed], t1.[Current Date]) < t1.[Next Adjustment Date];