IIF statement ?

[newbieaccess]

Can anyone please help? I have a Booking Qtr field that if the [ActivityDate] is <2/1/2007 then it should using a Calendar year otherwise it should be using : Format(DateAdd("m",11[ActivityDate]),"yyyy - q")

for example ActivityDate = 1/1/2006 the field should report 2006-1
ActivityDate = 2/1/2007 it should report 2008-1

Thank you, Thank you!!!!

 

[pbaldy]

How about

IIf([ActivityDate] < #2/1/2007#, OneFormatHere, OtherFormatHere)

 

[newbieaccess]

Hi, thanks! I'm trying to do this thru an update Qry but it's not letting me? can you not do an IIF in the Update to:

 

[pbaldy]

I haven't tried it (wouldn't store a calculated field), but I would think it would work. What's the SQL of the query, and what error do you get?

 

[newbieaccess]

Hi Paul, thank you for your help. It's much appreciated. Here's the SQL from my initial update query till I realized I had to account for dates prior to 2/1/2007. When I add the IIF to it, it disappears right after I hit okay. Is there another way for me to do this. I run this query after I append a bunch of data in

UPDATE [DATA 2] SET [DATA 2].Booking_Qtr = Format(DateAdd("m",11,[Activitydate]),"yyyy - q"), [DATA 2].Booking_Year = Year(DateAdd("m",11,[Activitydate])), [DATA 2].Qtr = Format(DateAdd("m",11,[Activitydate]),"q"), [DATA 2].Renewal_Qtr = Format(DateAdd("m",11,[Deal_End_Dt]),"yyyy - q"), [DATA 2].Renewal_Year = Year(DateAdd("m",11,[Deal_End_dt]));

 

[newbieaccess]

I've played with this a little more - here's my IIF statement now
IIf([Activitydate]>#2/1/2007#,Format(DateAdd("m",11,[Activitydate),"yyyy - q",Format(DateAdd("m",12,[Activitydate]),"yyyy - q")))
but now it's saying I can't update the records due to a type conversion failure? I'm confused because it worked when I didn't include the criteria of the date?

 

[pbaldy]

It appears like your parentheses are off. There are too many at the end, and the first Format() function was never closed off.

 

[newbieaccess]

Thank you!!! I really appreciate it.

 

[pbaldy]

No problem, and welcome to the site by the way!

 

[newbieaccess]

Thank you! Being new to this, it's a really nice resource. And you guys are great!