#Error using Iif(x,0,Trim(Right([NettingGrp],20))

[ragtopcaddy via AccessMonster.com]

I get a #error returned if the following Iif statement is false:

Netting_Group: IIf(Trim([NettingGrp])=0,0,Trim(Right([NettingGrp],20)))

Meanwhile, the following statement returns the string expected:

RtNtgGrp: Trim(Right([NettingGrp],20))

Why should the Iif statement return #Error when the same Trim(Right(
functions return a valid result in the same query?

 

[John Spencer]

Perhaps you mean to test the length of NettingGrp as in

IIF(Len(Trim(NettingGrp& ""))=0, "0", Trim(Right([NettingGrp],20)))

 

[Marshall Barton]

I get a #error returned if the following Iif statement is false:

Netting_Group: IIf(Trim([NettingGrp])=0,0,Trim(Right([NettingGrp],20)))

Meanwhile, the following statement returns the string expected:

RtNtgGrp: Trim(Right([NettingGrp],20))

Why should the Iif statement return #Error when the same Trim(Right(
functions return a valid result in the same query?

I don't know, but your mix of numeric and text values may be
confusing things.

How can the NettingGrp field equal 0? Shouldn't the
calculation be:
IIf(Trim([NettingGrp])="0","0",Trim(Right([NettingGrp],20)))

Or maybe that's not what you want to do and the IIf
condition is incomplete??

Maybe I could get a better picture of what you're trying to
do here if you posted a small set of sample data and the
desired result.

 

[ragtopcaddy via AccessMonster.com]

John,

Thanks for your response.

The field comes from a server db via odbc. It has a field size of 25
characters. The field will contain either a "0" followed by 24 spaces, or a
numerical value, <20 characters, preceded by the string "INS: ". This "INS:
" is redundant so I want to strip it from those fields that are <>0 before I
trim it. I can do that with no problem in a field by itself. I can work
around the problem by doing that in a sub-query and using that as the source
for this query.

UPDATE

OK, this is bizarre! Testing for 0 as stated yields a #Error for non-0 values.
But the following version yields correct results:

Netting_Group: IIf(Left([NettingGrp],4)="INS:",Trim(Right([NettingGrp],20)),0)


Strange.

Thanks,

Bill

Perhaps you mean to test the length of NettingGrp as in

IIF(Len(Trim(NettingGrp& ""))=0, "0", Trim(Right([NettingGrp],20)))

I get a #error returned if the following Iif statement is false:

[quoted text clipped - 6 lines]

Why should the Iif statement return #Error when the same Trim(Right(
functions return a valid result in the same query?

 

[ragtopcaddy via AccessMonster.com]

Marshall,

Thanks for your response. You area correct. When I edited the field as
follows, it returned the correct values:

Netting_Group: IIf(Trim([NettingGrp])="0","0",Trim(Right([NettingGrp],20)))

Thanks,

Bill

I get a #error returned if the following Iif statement is false:

[quoted text clipped - 6 lines]

Why should the Iif statement return #Error when the same Trim(Right(
functions return a valid result in the same query?

I don't know, but your mix of numeric and text values may be
confusing things.

How can the NettingGrp field equal 0? Shouldn't the
calculation be:
IIf(Trim([NettingGrp])="0","0",Trim(Right([NettingGrp],20)))

Or maybe that's not what you want to do and the IIf
condition is incomplete??

Maybe I could get a better picture of what you're trying to
do here if you posted a small set of sample data and the
desired result.

 

[John Spencer]

Your original then should have read more like the following. Note that you
are testing for a string value not a number value.

Netting_Group: IIf(Trim([NettingGrp])="0","0",Trim(Right([NettingGrp],20)))

I might have gone with
Netting_Group: IIf(Left([NettingGrp],1)="0","0",Mid([NettingGrp],5))

John,

Thanks for your response.

The field comes from a server db via odbc. It has a field size of 25
characters. The field will contain either a "0" followed by 24 spaces, or
a
numerical value, <20 characters, preceded by the string "INS: ". This
"INS:
" is redundant so I want to strip it from those fields that are <>0 before
I
trim it. I can do that with no problem in a field by itself. I can work
around the problem by doing that in a sub-query and using that as the
source
for this query.

UPDATE

OK, this is bizarre! Testing for 0 as stated yields a #Error for non-0
values.
But the following version yields correct results:

Netting_Group:
IIf(Left([NettingGrp],4)="INS:",Trim(Right([NettingGrp],20)),0)


Strange.

Thanks,

Bill

Perhaps you mean to test the length of NettingGrp as in

IIF(Len(Trim(NettingGrp& ""))=0, "0", Trim(Right([NettingGrp],20)))

I get a #error returned if the following Iif statement is false:

[quoted text clipped - 6 lines]

Why should the Iif statement return #Error when the same Trim(Right(
functions return a valid result in the same query?

 

[ragtopcaddy via AccessMonster.com]

John,

Thanks for your response.

That is the solution. If the whole Iif had failed, it would have been easier
to spot the problem, but Iif(Left(NettingGrp],1)=0 returned 0 just the same
as IIf(Trim([NettingGrp])="0", which served to mask the problem.

Bill R

Your original then should have read more like the following. Note that you
are testing for a string value not a number value.

Netting_Group: IIf(Trim([NettingGrp])="0","0",Trim(Right([NettingGrp],20)))

I might have gone with
Netting_Group: IIf(Left([NettingGrp],1)="0","0",Mid([NettingGrp],5))

John,

[quoted text clipped - 36 lines]