IIF statement syntax (2003)

Topic

New Reply

I have a query where I am comparing two tables – Augdata and Sepdata. I join the tables by a unique Id. The fields I am comparing have Y and N in them. Two fields I am comparing are SepHealth and AugHealth. I want a third column that I will call GAin/Loss/No Change. I want an IIF statement that says – IIF SepHealth = AugHealth then “No Change” else iif SepHealth = Y and AugHealth = N, “Gain” else iif SepHealth = N and AugHealth = Y, “Loss”. Can someone help me with the correct syntax for my Gain/Loss/No Change column?

Reply | Quote

Replies

Try this:

=IIf([SepHealth]=[AugHealth],"No Change",IIf([SepHealth]="Y" And [AugHealth]="N","Gain","Loss"))

This assumes that there are no blanks.

Reply | Quote

What if there are blanks. I am home so I am not sure about the data. How would I account for blanks?

Reply | Quote

What do you want to return if

1) AugHealth = Y and SepHealth is blank
2) AugHealth = N and SepHealth is blank
3) AugHealth is blank and SepHealth = Y
4) AugHealth is blank and SepHealth = N
5) AugHealth and SepHealth are both blank

Reply | Quote

1) AugHealth = Y and SepHealth is blank – Loss
2) AugHealth = N and SepHealth is blank – Loss
3) AugHealth is blank and SepHealth = Y – Gain
4) AugHealth is blank and SepHealth = N – Loss
5) AugHealth and SepHealth are both blank – No change

Reply | Quote

Let’s see. Try this expression:

IIf(IsNull([AugHealth]) And IsNull([SepHealth]) Or [AugHealth]=[SepHealth],"No Change",IIf([SepHealth]="Y","Gain","Loss"))


Reply | Quote

Thanks Hans – I will let you tomorrow if it works.

Reply | Quote