IIF Statement

[Mike]

I have two fields: Division, and an if statement field. I would like the if
statement to say "if the division = 3, or 4, or 5, or 6, or 7, then multiply
it by 1, if it doesn't, then input 99. I have the following but it is not
working.

Division: IIf([004 - Total Data w/ Retail-Cost]!Division=3 or 4 or 5 or 6 or
7,[004 - Total Data w/ Retail-Cost]!Division*1,99)

Thanks in advance.

 

[KARL DEWEY]

You will have problems having an alias with same name as your field so change
like below.
This works if [Division] is a number field and criteria is an uninterupted
sequence of numbers --
MyDivision: IIf([004 - Total Data w/ Retail-Cost].[Division] Between 3
AND 7,[004 - Total Data w/ Retail-Cost].[Division]*1,99)

For multiple criteria not in sequence use this --
MyDivision: IIf([004 - Total Data w/ Retail-Cost].[Division] = 3 OR [004
- Total Data w/ Retail-Cost].[Division] = 5 OR [004 - Total Data w/
Retail-Cost].[Division] = 7,[004 - Total Data w/ Retail-Cost].[Division]*1,99)

If Division is text then use quotes around the digit.

 

[John Spencer]

Why multiply by 1, that does not change the value.

IIF([Division] in (3,4,5,6,7),[Division],99)

If Division is a text field and you want to convert it, then

IIF([Division] in ("3","4","5","6","7"),Val([Division]),99)

You can do this with OR but you must have both sides of a comparison stated
IIF([Division]=3 OR [Division]=4 OR [Division]=5 OR [Division]=6 OR
[Division]=7, [Division]*1,99)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County