Help with IIf Statement

[Guest]

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))


I made this expression so that if a field contains info like "H45 RH" it
will display as "Supplement Page H45"; also so that if a field contains info
like "243 EL" it will display as "Catalog Page 243". If the field is null, it
stays null. It determines what to display by testing the string with the
IsNumeric () function.

But, some times the data that looks like:
RH (8 spaces following the initials)
EL (8 spaces following the initials)
JH (8 spaces following the initials)

I need to amend this statement so that if the field contains info like the
above, it'll just display it, and not display "Catalog Page" or "Supplement
Page". At this point, when it encounters data like the above, it displays
"Supplement Page RH".

I don't not where/how to edit this statement. Can anyone help?

Thanks in advance.
Jarrod

 

[James Hahn]

You need to replace the "Supplement Page " result value with a new iif
statement (yet another nested iif) that tests for the trailing blanks and
returns "Supplement Page " or "". For instance:
PageNum: IIf(IsNull([ShopCartSmall - terms].[PageNum]),"",
IIf(IsNumeric(Left([ShopCartSmall - terms].[PageNum],3)),"Catalog Page ",
IIf(Mid([ShopCartSmall - terms].[PageNum],3,1) = " ", "Supplement Page
","")) &
Left([ShopCartSmall - terms].[PageNum],3) & " " &
Right([ShopCartSmall - terms].[PageNum],2))

 

[John Vinson]

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))


I made this expression so that if a field contains info like "H45 RH" it
will display as "Supplement Page H45"; also so that if a field contains info
like "243 EL" it will display as "Catalog Page 243". If the field is null, it
stays null. It determines what to display by testing the string with the
IsNumeric () function.

But, some times the data that looks like:
RH (8 spaces following the initials)
EL (8 spaces following the initials)
JH (8 spaces following the initials)

Are you certain that's what's stored? If this is an Access table,
trailing blanks will usually be removed.

I need to amend this statement so that if the field contains info like the
above, it'll just display it, and not display "Catalog Page" or "Supplement
Page". At this point, when it encounters data like the above, it displays
"Supplement Page RH".

I'd suggest that you're getting too deep in nested IIF's to be
practical. A useful alternative is the Switch() function; it takes
arguments in pairs, and goes through the pairs left to right. If the
first member of a pair is true it returns the second of that pair and
quits. So you could have:

Switch(
IsNull([PageNum], "",
[PageNum] LIKE "?? *", "",
IsNumeric(Left([PageNum], 3)), "Catalog Page ",
True, "Supplement Page ")

or whatever options you need.

John W. Vinson[MVP]