Switch Function w/nested IIf Functions

[DaveB]

Hello:

The expression listed below works perfectly in a query, but when I use
it as an expression to group on within a report, I get the following
error message:

"The expression you entered is missing a closing parenthesis, bracket
(]), or verticle bar (|).

Switch(IIf([Sold Price] Is Not Null,[Sold Price],[Curr List
Price])<1500000,"$1,000,000 - $1,499,999",IIf([Sold Price] Is Not Null,
[Sold Price],[Curr List Price])<2000000,"$1,500,000 -
$1,999,999",IIf([Sold Price] Is Not Null,[Sold Price],[Curr List
Price])<4000000,"$2,000,000 - $3,999,999",True,"$4,000,000+")

When using the expression listed above in a query, it's preceded by
"Field:", and when using it in a report, it's preceded by "=".

Thanks in advance for any assistance,

Dave

 

[John Spencer]

Don't know why you are having the problem. You might try rewriting it like

SWITCH( NZ(Sold Price],[Curr List Price])<1500000,"$1,000,000 - $1,499,999",
NZ(Sold Price],[Curr List Price])<2000000,"$1,500,000 - $1,999,999",
NZ(Sold Price],[Curr List Price])<4000000 ,"$2,000,000 - $3,999,999",
True,"$4,000,000+")

Hello:

The expression listed below works perfectly in a query, but when I use
it as an expression to group on within a report, I get the following
error message:

"The expression you entered is missing a closing parenthesis, bracket
(]), or verticle bar (|).

Switch(IIf([Sold Price] Is Not Null,[Sold Price],[Curr List
Price])<1500000,"$1,000,000 - $1,499,999",IIf([Sold Price] Is Not Null,
[Sold Price],[Curr List Price])<2000000,"$1,500,000 -
$1,999,999",IIf([Sold Price] Is Not Null,[Sold Price],[Curr List
Price])<4000000,"$2,000,000 - $3,999,999",True,"$4,000,000+")

When using the expression listed above in a query, it's preceded by
"Field:", and when using it in a report, it's preceded by "=".

Thanks in advance for any assistance,

Dave

--

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