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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|