Nested IIF Statements

[MichaelR]

Hi,

I have a database with sales data that I import into excel and then
manipulate with pivot tables/charts. I also added my budget numbers to the
sales data table but left a lot of the fields blank. For example, for the
sales data, there is a company, a division, a sales rep, a product, a
department, a year, a month and a few other fields. I imported the budgeted
data and left the company and division columns blank and wrote "BUDGET" in
the department column so that I would be able to distinguish the actual sales
from the budgeted sales in the data table.

I wrote a query that returns a combined data table (which is the one that I
import into excel) and I am trying to add a column/field that will determine
whether each row is Current Year Budget (CYB), Current Year Actual (CYA),
Prior Year Actual (PYA) or 2006 Actual (2006A).

Logically, the expression that I am trying to write should do the following:
IF [DEPT]="Budget" then "CYB" Else IF [YEAR]=2008 then "CYA" Else IF [YEAR]
= 2007 then "PYA" Else "2006A"

The formula that I wrote doesn't work. It returns 2006A for the budget
numbers and and error message for every other row. It looks like this:

Period: IIf([CSDEPT] = "BUDGET", "CYB", IIf([CSFYR]=2007, "PYA",
IIf([CSFYR]=2008, "CYA", "2006A"
)))

Any ideas? Please help.

Thanks,
Michael

 

[Dennis]

What error message are you getting because there is nothing wrong with your
nested IIF statement ?
What are the data types for fields CSDEPT & CSFYR ?

 

[MichaelR]

The data type for CSDEPT is text and for CSFYR is number. The error was
showing up in the rows with 2008, 2007 and 2006 actual data.

I just tried the expression again (on your recommendation) and it worked,
even though it wasn't working yesterday.

Thanks, Dennis!!

What error message are you getting because there is nothing wrong with your
nested IIF statement ?
What are the data types for fields CSDEPT & CSFYR ?

Hi,

I have a database with sales data that I import into excel and then
manipulate with pivot tables/charts. I also added my budget numbers to the
sales data table but left a lot of the fields blank. For example, for the
sales data, there is a company, a division, a sales rep, a product, a
department, a year, a month and a few other fields. I imported the budgeted
data and left the company and division columns blank and wrote "BUDGET" in
the department column so that I would be able to distinguish the actual sales
from the budgeted sales in the data table.

I wrote a query that returns a combined data table (which is the one that I
import into excel) and I am trying to add a column/field that will determine
whether each row is Current Year Budget (CYB), Current Year Actual (CYA),
Prior Year Actual (PYA) or 2006 Actual (2006A).

Logically, the expression that I am trying to write should do the following:
IF [DEPT]="Budget" then "CYB" Else IF [YEAR]=2008 then "CYA" Else IF [YEAR]
= 2007 then "PYA" Else "2006A"

The formula that I wrote doesn't work. It returns 2006A for the budget
numbers and and error message for every other row. It looks like this:

Period: IIf([CSDEPT] = "BUDGET", "CYB", IIf([CSFYR]=2007, "PYA",
IIf([CSFYR]=2008, "CYA", "2006A"
)))

Any ideas? Please help.

Thanks,
Michael