A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old July 27th, 2006, 06:37 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

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?

--
Bill Reed

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

Message posted via http://www.accessmonster.com

  #2  
Old July 27th, 2006, 07:07 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

Perhaps you mean to test the length of NettingGrp as in

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


"ragtopcaddy via AccessMonster.com" u9289@uwe wrote in message
news:63e1d90ee212f@uwe...
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?

--
Bill Reed

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

Message posted via http://www.accessmonster.com



  #3  
Old July 27th, 2006, 07:25 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

ragtopcaddy via AccessMonster.com wrote:

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.

--
Marsh
MVP [MS Access]
  #4  
Old July 27th, 2006, 07:33 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

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

John Spencer wrote:
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?


--
Bill Reed

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

Message posted via http://www.accessmonster.com

  #5  
Old July 27th, 2006, 07:39 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

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

Marshall Barton wrote:
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.


--
Bill Reed

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

Message posted via http://www.accessmonster.com

  #6  
Old July 28th, 2006, 12:51 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

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


"ragtopcaddy via AccessMonster.com" u9289@uwe wrote in message
news:63e2556a5b0ef@uwe...
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

John Spencer wrote:
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?


--
Bill Reed

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

Message posted via http://www.accessmonster.com



  #7  
Old July 28th, 2006, 01:05 PM posted to microsoft.public.access.queries
ragtopcaddy via AccessMonster.com
external usenet poster
 
Posts: 67
Default #Error using Iif(x,0,Trim(Right([NettingGrp],20))

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

John Spencer wrote:
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?


--
Bill Reed

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200607/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.