CODING

  • Thread starter Thread starter CODE
  • Start date Start date
C

CODE

IIF(Instr([Names]," ")>0,LEFT([Names],Instr([Names]," ")-
1),Instr([Names],",")>0,LEFT([Names],Instr([Names],",")-1),
[Names])


WHat is wrong with this expression? It tells me I have
the wrong number of arguments.
 
Dear Code (Cody?):

I think your logic requires 2 IIf() statements nested.

I would recommend you use the IIf() to determine where the space or
comma occurs, probably using the whole string length if there is
neither.

LEFT(Names,
IIf(Instr(Names, " ") > 0,
Instr(Names, " ") - 1,
IIf(Instr(Names, ",") > 0,
Instr(Names, ",") - 1,
Len(Names))))

This avoids the possibility of usint LEFT() with any unintended
values. Try to remember this: IIf() always must evaluate ALL its
arguments, even if the argument turns out to be unused in the result.
The values of the 3 arguments to IIf() are all evaluated before the
function is called! (Just like any other function.) So, if the
values are illegal, you can get an error message due to an argument
whose value you actually aren't going to use.

With LEFT() this cannot happen, as 0 is allowed. However, this is
just a practice I follow anyway.

I also think the code documents all the cases that can occur,
especially the one where there is neither a space nor a comma in
Names. I show it as returning the whole string, but you can now
readily change it to returrn "*** error error error ***" or whatever
is appropriate.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
THANK YOU THANK YOU THANK YOU!!!!
-----Original Message-----
Dear Code (Cody?):

I think your logic requires 2 IIf() statements nested.

I would recommend you use the IIf() to determine where the space or
comma occurs, probably using the whole string length if there is
neither.

LEFT(Names,
IIf(Instr(Names, " ") > 0,
Instr(Names, " ") - 1,
IIf(Instr(Names, ",") > 0,
Instr(Names, ",") - 1,
Len(Names))))

This avoids the possibility of usint LEFT() with any unintended
values. Try to remember this: IIf() always must evaluate ALL its
arguments, even if the argument turns out to be unused in the result.
The values of the 3 arguments to IIf() are all evaluated before the
function is called! (Just like any other function.) So, if the
values are illegal, you can get an error message due to an argument
whose value you actually aren't going to use.

With LEFT() this cannot happen, as 0 is allowed. However, this is
just a practice I follow anyway.

I also think the code documents all the cases that can occur,
especially the one where there is neither a space nor a comma in
Names. I show it as returning the whole string, but you can now
readily change it to returrn "*** error error error ***" or whatever
is appropriate.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


IIF(Instr([Names]," ")>0,LEFT([Names],Instr([Names]," ")-
1),Instr([Names],",")>0,LEFT([Names],Instr([Names],",")- 1),
[Names])


WHat is wrong with this expression? It tells me I have
the wrong number of arguments.
Click to expand...

.
Click to expand...
 
Back
Top