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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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@xxxxxx
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



.

Relevant Pages

  • Re: get max date from linked sql server table
    ... It's a date/time field, Mmm dd, ... and I'm not familiar enough with TSQL to put the query together. ... MAX BEFORE you convert it to a string. ... "If you can't laugh at yourself, ...
    (microsoft.public.access.queries)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)