IIF Statement with multiple conditions - output cost incomplete

[MLatte]

I'm having issues with my query where I need to look at four columns to get my output cost in Completed FY YR Cost2 column. For the most part I can get it to work except I cannot get the FY 21-22 Costs. Also, the reason why I have to identify status "Completed" is because I also have costs with status "Active" which I do not want to show those costs in the completed cost column.



I know there's an order here, so I've rearranged the statements which made sense (to me) but then the cost drops elsewhere...so, now I'm stumped.

Query:
Completed FY YR Cost2: IIf([Status]="Completed" And [FY 22-23 Costs]>[FY 21-22 Costs],[FY 22-23 Costs],IIf([Status]="Completed" And [FY 20-21 Costs] Is Null,[FY 22-23 Costs],IIf([Status]="Completed" And [FY 21-22 Costs] Is Null,[FY 20-21 Costs],IIf([Status]="Completed" And [FY 22-23 Costs] Is Null,[FY 21-22 Costs]))))


Datasheet view?

FY 20-21 Costs	FY 21-22 Costs	FY 22-23 Costs	Status	Completed FY YR Cost2
$21,485.59	$25,435.66		Completed	$25,435.66
$45,319.58	$53,498.53		Completed	$53,498.53
$14,649.58	$16,804.15	$18,830.26	Completed	$18,830.26
$17,860.05	$23,616.85	$26,571.20	Completed	$26,571.20
$19,255.64			Completed	$19,255.64
$11,828.62			Completed	$11,828.62
	$340,029.92		Completed
	$184,250.49		Completed
		$62,724.61	Completed	$62,724.61
		$68,997.07	Completed	$68,997.07

[June7]

You want 22-23 only if it is > 21-22?

Shouldn't have to repeat the Completed condition.

IIf([Status]="Completed", IIf([FY 22-23 Costs]>[FY 21-22 Costs],[FY 22-23 Costs],IIf([FY 20-21 Costs] Is Null, [FY 22-23 Costs],IIf([FY 21-22 Costs] Is Null,[FY 20-21 Costs],IIf([FY 22-23 Costs] Is Null,[FY 21-22 Costs])))), 0)

If 20-21 Is Null it returns 22-23 even if 22-23 Is Null, the calc never gets to the last IIf()

State in English the rules you want to code. If you only want the first value that isn't Null:

IIf([Status]="Completed", Nz([FY 22-23 Costs], Nz([FY 21-22 Costs], [FY 20-21 Costs])),0)

[MLatte]

Hi June7,

Essentially I need the last cost from the columns.
1) Although you cannot see the status "Active" costs but they're there, so I'm only looking at the status "Completed" to populate the costs.
- If there is FY 20-21 Costs and FY 21-22 Costs: I need the FY 21-22 Costs
- If there is FY 20-21 Costs and FY 21-22 Costs and FY 22-23 Costs: I need the FY 22-23 Costs
- Only FY 20-21 Costs: I need FY 20-21 Costs
- Only FY 21-22 Costs: I need FY 21-22 Costs
- Only FY 22-23 Costs: I need FY 22-23 Costs

Hope this makes sense.

Thanks.

[June7]

And doesn't the expression I suggested do that?

[MLatte]

And doesn't the expression I suggested do that?

Sorry! I hadn't checked...but yes, the expression worked.

Thanks!