4 way IIF with nulls?

[exceliz]

Hi experts..

I cannot get my iif statement to work in access query; its driving me mad, just cant seem to grasp the nulls I think.

I need to calculated a waiting time with adjustments, I have a field called RTT which is the current wait in days without adjustments.
I have a field called total suspension which is the number of days to deduct from RTT if populated, and if there is a DNA Date I need to work out the RTT from DNA date to todays date to give the new RTT days waiting....But they could both apply, or just one apply or non apply?

this is what i had but doesnt work for all IIFs: ive switched them around and tried isnull etc
RTTdADJ:
IIf([DNADate] Is Not Null,DateDiff("d",[DNADate],Date()),IIf([DNADate] And [Total Suspension] Is Null,[RTT],IIf([DNADate] And [Total Suspension] Is Not Null,(DateDiff("d",[DNADate],Date()))-[Total Suspension],[RTT]-[Total Suspension])))

thank you for any advice on this one i will keep trying in the mean time

 

[Joe4]

I think you may have a Syntax issue.

Look at this part here:

[COLOR=#333333]IIf([DNADate] And [Total Suspension] Is Null[/COLOR]

Are you trying to check to see if BOTH are Null? If so, you need to write it like this:

[COLOR=#333333]IIf([DNADate] [/COLOR][COLOR=#ff0000]Is Null[/COLOR][COLOR=#333333] And [Total Suspension] Is Null[/COLOR]

Otherwise, your code is just checking to see if a value for DNADate exists (not if it is null).

 

[exceliz]

thanks, i have managed to re do it and it works!! - it was all about the nulls. Total suspension was only populated if you had a suspension.
IIf(IsDate([DNADate]),DateDiff("d",[DNADate],Date())-IIf([Total Suspension] Is Null,0,[Total Suspension]),IIf([Total Suspension] Is Not Null,[RTT]-[Total Suspension],[RTT]))