IIF Function

[MichaelR]

Hi,

I'm trying to put an IIF function into a query in access that will look at
the year and then return either CYA (Current Year Actual) if the year is
2008, PYA (Prior Year Actual) if the year is 2007 or a blank if the year is
2006 or less.

The IF statement that I wrote looks like this:

Period: IIf([CSFYR]=2007,"PYA",IIf([CSFYR]="2008","CYA",Null)).

When I try to run the query, an error message appears that says "Data Type
Mismatch in Criteria Expression."

Any ideas for how I can make this formula work?

Thanks!
Michael

 

[BruceM]

If CSFYR is text, enclose the year in quotes as you have done for 2008. If
it is number, use no quotes, as you have done for 2007. In either case, try
"" instead of Null.
If there is already a date field you shouldn't have a separate Year field.
Instead:
IIf(Year([DateField]) = 2008,"PYA",IIf(Year([DateField]) = 2007,"CYA",""))

 

[Dennis]

Your first check on the year is as a number but your second is as a string.
Also, if you want it to be blank then use "" instead of Null

Period: IIf([CSFYR]=2007,"PYA",IIf([CSFYR]=2008,"CYA",""))

 

[MichaelR]

Thank you both for your help. The formula worked when I took the "" off of
2008.

I changed my formula a little to:

Period: IIf([CSFYR]=2008 AND [CSCTYP]=“â€,"CYB",IIf([CSFYR]=2008 AND
[CSCTYP]<>â€â€,"CYA",IIF(
[CSFYR]=2007,â€PYAâ€,â€2006Aâ€)))

and now access asks me to "enter parameter value" for "" when I try to run
the query. Any idea why Access isn't accepting "" as a blank?

Thanks,
Michael

Your first check on the year is as a number but your second is as a string.
Also, if you want it to be blank then use "" instead of Null

Period: IIf([CSFYR]=2007,"PYA",IIf([CSFYR]=2008,"CYA",""))

Hi,

I'm trying to put an IIF function into a query in access that will look at
the year and then return either CYA (Current Year Actual) if the year is
2008, PYA (Prior Year Actual) if the year is 2007 or a blank if the year is
2006 or less.

The IF statement that I wrote looks like this:

Period: IIf([CSFYR]=2007,"PYA",IIf([CSFYR]="2008","CYA",Null)).

When I try to run the query, an error message appears that says "Data Type
Mismatch in Criteria Expression."

Any ideas for how I can make this formula work?

Thanks!
Michael