Critical IIf/Switch condition

[prasadavasare]

I have a table with three fields: two Date fields [StartDate](without null values/No Blanks) and [CONVDate](with some null values/Blanks) text[Frequency] (e.g "Annual", "Quarterly", "Monthly" without null values/no blanks).


I want to create a query with an additional date field which will calculate as follows:

[CalculatedDate]: IIf([CONVDate] Is Null, IIf([Frequency]="Annual",[StartDate]+365, IIf([Frequency]="Quarterly",[StartDate]+90,IIf([Frequency]="Monthly",[StartDate]+30))),[CONVDate])


The problem is I am getting row results for only items where [CONVDate] is not null.....pls help

 

[Joe4]

It seems to work fine foe me.
Is CONVDate declared as a Date or Text Data type?

Are you sure the values are Null?
An easy way to check is to use this expression in a calculated field:

Null Check: [CONVDate] Is Null

Does that return -1 for the CONVDate records that you think are Null?

Note, if you are looking to add EXACTLY 12, 3, or 1 month respectively, instead of adding days, you can use the DATEADD function, i.e.

CalculatedDate: IIf([CONVDate] Is Null,IIf([Frequency]="Annual",DateAdd("m",12,[StartDate]),IIf([Frequency]="Quarterly",DateAdd("m",3,[StartDate]),IIf([Frequency]="Monthly",DateAdd("m",1,[StartDate])))),[CONVDate])

 

[prasadavasare]

Hi Joe4,

Thank you so much for your assistance. I think I found the error. Actually the value I thought was actually not Null...rather it was ""...I changed it in query and it works fine.

Thank you so much again!!