find("this",A5)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any statement in Access query mode comparable to the Excel statement
=find("this",A5) .... which returns the position number of "this" in cell
A5.

Thanks.
 
Karl,
Thanks - that works great. Is it possible to embed that statment i.e., ..
Expr1: InStr([Field1],"u") into a mid statement in access? Similar in Excel
to =Mid("A4",FIND("u","A4"),7) ---- which results in a string 7 chararcters
long at the beginning of the u in cell A4.

Steve

KARL DEWEY said:
Access does not have calls.
You might try --- Instr([YourField], "this")

--
KARL DEWEY
Build a little - Test a little


Steve Stad said:
Is there any statement in Access query mode comparable to the Excel statement
=find("this",A5) .... which returns the position number of "this" in cell
A5.

Thanks.
Click to expand...
Click to expand...
 
There is a MID function in Access VBA so

MID([Field1], Instr(1,[Field1],"u"),7)
If you want the next 7 characters after the U
MID([Field1], Instr(1,[Field1],"u")+1,7)

One problem is that if there is no U in the field then the first seven
characters will be returned.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Stad said:
Karl,
Thanks - that works great. Is it possible to embed that statment i.e., ..
Expr1: InStr([Field1],"u") into a mid statement in access? Similar in
Excel
to =Mid("A4",FIND("u","A4"),7) ---- which results in a string 7
chararcters
long at the beginning of the u in cell A4.

Steve

KARL DEWEY said:
Access does not have calls.
You might try --- Instr([YourField], "this")

--
KARL DEWEY
Build a little - Test a little


Steve Stad said:
Is there any statement in Access query mode comparable to the Excel
statement
=find("this",A5) .... which returns the position number of "this" in
cell
A5.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
One problem is that if there is no U in the field then the first seven
characters will be returned.
A way around this is as follows --
IIF(Instr(1,[Field1],"u")<0, Null, MID([Field1], Instr(1,[Field1],"u"),7))
It check for a 'u' first.
--
KARL DEWEY
Build a little - Test a little


John Spencer said:
There is a MID function in Access VBA so

MID([Field1], Instr(1,[Field1],"u"),7)
If you want the next 7 characters after the U
MID([Field1], Instr(1,[Field1],"u")+1,7)

One problem is that if there is no U in the field then the first seven
characters will be returned.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Stad said:
Karl,
Thanks - that works great. Is it possible to embed that statment i.e., ..
Expr1: InStr([Field1],"u") into a mid statement in access? Similar in
Excel
to =Mid("A4",FIND("u","A4"),7) ---- which results in a string 7
chararcters
long at the beginning of the u in cell A4.

Steve

KARL DEWEY said:
Access does not have calls.
You might try --- Instr([YourField], "this")

--
KARL DEWEY
Build a little - Test a little


:

Is there any statement in Access query mode comparable to the Excel
statement
=find("this",A5) .... which returns the position number of "this" in
cell
A5.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
John - The second line works but as you mentioned it does not include the U.
The first line returned an error message saying...Invalid procedure call.
What is wrong with the first line -- Expr2:
Mid([Field1],InStr(1,[Field1],"u2"),6)

Thanks
Steve

John Spencer said:
There is a MID function in Access VBA so

MID([Field1], Instr(1,[Field1],"u"),7)
If you want the next 7 characters after the U
MID([Field1], Instr(1,[Field1],"u")+1,7)

One problem is that if there is no U in the field then the first seven
characters will be returned.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Stad said:
Karl,
Thanks - that works great. Is it possible to embed that statment i.e., ..
Expr1: InStr([Field1],"u") into a mid statement in access? Similar in
Excel
to =Mid("A4",FIND("u","A4"),7) ---- which results in a string 7
chararcters
long at the beginning of the u in cell A4.

Steve

KARL DEWEY said:
Access does not have calls.
You might try --- Instr([YourField], "this")

--
KARL DEWEY
Build a little - Test a little


:

Is there any statement in Access query mode comparable to the Excel
statement
=find("this",A5) .... which returns the position number of "this" in
cell
A5.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Well that will error if there is no U2 in the field.

It is best to test for the presence of the value first as Karl Dewey
suggested.

IIF(Instr(1,[FieldName]& "","U2")=0, Null ,
MID([FieldName],Instr(1,[FieldName],"U2"),7) )

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Steve said:
John - The second line works but as you mentioned it does not include the U.
The first line returned an error message saying...Invalid procedure call.
What is wrong with the first line -- Expr2:
Mid([Field1],InStr(1,[Field1],"u2"),6)

Thanks
Steve

John Spencer said:
There is a MID function in Access VBA so

MID([Field1], Instr(1,[Field1],"u"),7)
If you want the next 7 characters after the U
MID([Field1], Instr(1,[Field1],"u")+1,7)

One problem is that if there is no U in the field then the first seven
characters will be returned.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Steve Stad said:
Karl,
Thanks - that works great. Is it possible to embed that statment i.e., ..
Expr1: InStr([Field1],"u") into a mid statement in access? Similar in
Excel
to =Mid("A4",FIND("u","A4"),7) ---- which results in a string 7
chararcters
long at the beginning of the u in cell A4.

Steve

:

Access does not have calls.
You might try --- Instr([YourField], "this")

--
KARL DEWEY
Build a little - Test a little


:

Is there any statement in Access query mode comparable to the Excel
statement
=find("this",A5) .... which returns the position number of "this" in
cell
A5.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top