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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ragtopcaddy - 27 Jul 2006 18:37 GMT

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?

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

John Spencer - 27 Jul 2006 19:07 GMT

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?

Reply to this Message

ragtopcaddy - 27 Jul 2006 19:33 GMT

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
>
[quoted text clipped - 5 lines]
>> Why should the Iif statement return #Error when the same Trim(Right(
>> functions return a valid result in the same query?

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

John Spencer - 28 Jul 2006 12:51 GMT

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]
>>> Why should the Iif statement return #Error when the same Trim(Right(
>>> functions return a valid result in the same query?

Reply to this Message

ragtopcaddy - 28 Jul 2006 13:05 GMT

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.
[quoted text clipped - 9 lines]
>>>> Why should the Iif statement return #Error when the same Trim(Right(
>>>> functions return a valid result in the same query?

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message

Marshall Barton - 27 Jul 2006 19:25 GMT

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

ragtopcaddy - 27 Jul 2006 19:39 GMT

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 - 15 lines]
>do here if you posted a small set of sample data and the
>desired result.

Signature

Bill Reed

"If you can't laugh at yourself, laugh at somebody else"

Reply to this Message