Update query w/IIf and DateAdd functions 1

[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!!

 

[JaneC]

Hi Dawn,

In what way doesn't it work ??

The only thing is that you might want to try Null instead of 0 as when I try it, using 0 replaces the date with 30/12/99

HTH

JAne

 

[Dawnit]

Thank you Jane! I tried Null too. Didn't work. Basically, when I combine the IIf formula with the DateAdd, it ignores both and puts in some generic date.

Is the problem related to the update query (with said formula)linked to too manay tables (2)?

 

[Dawnit]

Jane, you were right after all. Null worked! Thank you so very much!

 

[JaneC]

No problem, glad to be able to help !