Conditional IIF Statement ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have written a query that returns the following results:

MonthKey CC# Bal Income Account Number

199712 66 911,568.51 49103022
199712 66 0.00 49103098
199712 66 0.00 49103157
199812 66 2,353,987.67 49103022
199812 66 0.00 49103098
199812 66 0.00 49103157
199912 66 11,260,690.37 100
199912 66 2,554,689.47 49103022
199912 66 0.00 49103098
199912 66 0.00 49103157
200012 66 14,027,577.54 100
200012 66 2,808,225.45 49103022

I would like to add a fifth column called Type that returns "Revenue" if the
Account Number equals 100 or 49103022 and "Reversal" if not. I am using the
following iif function which returns "Revenue" for all records:

Type: IIF([Account Number]=100 or 49103022,"Revenue","Reversal")

I suspect a problem with the "or". When I remove "or 49103022" the function
returns "Revenue" for records where Account Number is equal to 100 and
"Reversal" for all other records, but I want to return "Revenue" for records
where Account Number is equal to 100 or 49103022. Can anyone help?

Thanks,

Joe
 
You are correct. Use this --

Type: IIF([Account Number]=100 or [Account Number] = 49103022,
"Revenue","Reversal")
 
Hi, Joe.

Your expression is evaluating 49103022 as a boolean value. Include boolean
expressions on both sides of the OR operator. For clarity, I like to include
parentheses delimiters to emphasize that the enclosed expression is returning
a single value.

Type: IIF(([Account Number]=100 or [Account Number]
=49103022),"Revenue","Reversal")

Hope that helps.

Sprinks
 
Back
Top