nested Iif statement ques

E

ExcessAccess

How can I modify the following statement, so that it will do nothing (display
PageNum as is) for records where [PageNum] is like "?? " ? (There are
8 spaces after the questions mark.)

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric(Left([ShopCartSmall -
terms].[PageNum],3)),"Catalog Page ","Supplement Page ") &
Left([ShopCartSmall - terms].[PageNum],3) & "  " &
Right([ShopCartSmall - terms].[PageNum],2))

Jarrod
 
J

John Vinson

How can I modify the following statement, so that it will do nothing (display
PageNum as is) for records where [PageNum] is like "?? " ? (There are
8 spaces after the questions mark.)

PageNum: IIf(IsNull([ShopCartSmall -
terms].[PageNum]),"",IIf(IsNumeric(Left([ShopCartSmall -
terms].[PageNum],3)),"Catalog Page ","Supplement Page ") &
Left([ShopCartSmall - terms].[PageNum],3) & "  " &
Right([ShopCartSmall - terms].[PageNum],2))

Jarrod
Click to expand...

You're nesting IIF's too deep for my comfort already. I'd suggest
switching to the Switch() function; it's handy if you have several
conditions to test. The arguments to Switch come in pairs; the program
goes through them left to right, and the first time it finds a pair
for which the first argument is True it returns the second member of
that pair. So:

PageNum: Switch(
IsNull([PageNum]), "",
IsNumeric(Left([PageNum],3)),"Catalog Page ",
[PageNum] LIKE "?? ", [PageNum],
True, Left([PageNum],3) & "  " & Right([PageNum],2))

The last option will be used only if the first three "fall through",
i.e. none of the three conditions are met.

John W. Vinson[MVP]
 
Top