Add a lot of criteria 1

[Zonie32]

I have a field in my query that shows:
#months: IIf([mandate_state]='FL',DateDiff('m',#11/1/2003#,[RGExpDate]),DateDiff('m',#8/1/2003#,[RGExpDate]))

I want to add some more criteria to this. I have a field called cancel_date. I want to also say IF [cancel_date] is not null and [cancel_date]<[RGExpDate], then do DateDiff('m',#11/1/2003#,[cancel_date],DateDiff('m',#8/1/2003#,[cancel_date]).

So if the cancel date is not null, then subtract the datediffs by cancel date, but if the cancel date is null, then use the RGExpDate. Does this make sense? Any help is welcomed! thanks.

 

[DotNetGnat]

IIf([mandate_state]='FL',IIF(([Cancel_date] IS NOT NULL)AND ([Cancel_date]<[RGExpDate]), DateDiff('m',#11/1/2003#,[Cancel_date]),DateDiff('m',#8/1/2003#,[Cancel_date])))

-DNG

 

[Zonie32]

Thanks DotNetGnat for responding. This code is not producing the #months. they are just blank.

I need for the formula to use Cancel date to calculate the # months if Cancel Date is less than the RGExpDate, but if it isn't less than the RGExpDate then use the RGExpDate to calculate the # months.

 

[DotNetGnat]

ok i will write the logic here...you put the actual query

IIF(condition1 is true,IIF(condition2 is true, val1,val2))

if condition 1 is true and condition2 is true then you will get val1, if condition1 is true and condition2 is false then you will get val2...

so did u get it...

try it out...

-DNG

 

[Zonie32]

I am trying, but so far the #months is still blank. I can't seem to get it worked out.

 

[DotNetGnat]

post what you tried so far...

-DNG

 

[Zonie32]

I moved the IIF's around a bit, and this seems to work to come up with #months if case_cancel less than RGExpDate. But now I need the rest of it to show if the case_cancel date is > RGExpDate then use RGExpDate in the DateDiff portion of the formula.


#months: IIf([case_cancel]<[RGExpDate],IIf([mandate_state]='FL',DateDiff('m',#11/1/2003#,[case_cancel]),DateDiff('m',#8/1/2003#,[case_cancel])))

 

[Zonie32]

Correction, if case_cancel is null OR greater than RGExpDate, then use RGExpDate in the DateDiff portion.

 

[DotNetGnat]

#months: IIf([case_cancel]<[RGExpDate],IIf([mandate_state]='FL',DateDiff('m',#11/1/2003#,[case_cancel]),DateDiff('m',#8/1/2003#,[case_cancel])),IIf([mandate_state]='FL',DateDiff('m',#11/1/2003#,[RGExpDate]),DateDiff('m',#8/1/2003#,[RGExpDate])))

-DNG

 

[Zonie32]

Thank you DNG. That last one did the trick! You are awesome.

 

[DotNetGnat]

no problem...glad to be of help...

-DNG