Update query w/IIf and DateAdd formulas

[Dawnit]

Hi! I am having trouble with a formula in the Update to: of my update query. I can get it to update a date field in the underlying table with this formula:

DateAdd("yyyy",([table1]![Number1]+[table1]![Number2]),[table2]![Date])

but, if a check box in another field is checked, I want the same date field in the underlying table to become blank.

Does this make sense?

I tried this and it didn't work:

IIf([table1]![checkbox field]=Yes, 0,DateAdd("yyyy",([table1]![Number1]+[table1]![Number2]),[table2]![Date])

I would appreciate any and all help I can get. Thanks!!

 

[Liv Manto]

Try True or -1

[table1]![checkbox field]=True

 

[Rich]

You shouldn't store calculated date fields in this way use a query.

 

[Pat Hartman]

It is too late for me to really think about what you are trying to do, but, you would need to set the date to null in your IIf() rather than 0. 0 = Dec 31, 1899 which is probably not what you had in mind.

 

[Dawnit]

Yes! That's it. Thanks!!!