Search substring in a string

[rama]

Hello
In my database there is a table called tblImport, which is imported to
database from MS Excel. This tblImport is having some fields called
"JobNo", "Status" etc. JobNo field contains unique data where as
Status field contains data which are separated by a space. What I wish
to do is to extract a substring from "STATUS" and get the result in
the final status. In excel with nested IF I can get the result as
below

IF(ISNUMBER(SEARCH("PCNF",A3)),"PCNF",IF(ISNUMBER(SEARCH
("CNF",A3)),"CNF",IF(ISNUMBER(SEARCH("REL",A3)),"REL",IF(ISNUMBER
(SEARCH("CRTD",A3)),"CRTD"))))


STATUS FINAL_STATUS
REL PCNF NMAT PPRT PRC SETC PCNF
REL NMAT PRC SETC REL
REL CNF NMAT PRC SETC CNF
REL PRT GMPS MACM PRC SETC REL
CRTD PRC CRTD

How to do it with the help of a access query.
Thanks in advance
Rama

 

[Ken Snell MVP]

You can extract from a string using the Mid and InStr functions:

GetExtract: Mid(NameOfField, InStr(NameOfField, StringToFind),
Len(StringToFind))

 

[rama]

You can extract from a string using the Mid and InStr functions:

GetExtract: Mid(NameOfField, InStr(NameOfField, StringToFind),
Len(StringToFind))

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/







- Show quoted text -

Thank you very much for the response.
I tried like GetExtract: Mid([Status],InStr([Status],"PCNF"),Len
("PCNF")) and it will list out all "PCNF" but if the status field
doesn't have "PCNF" then the STATUS may contain "CNF" or "CRTD" or
"REL". What I wish to do is GetExtract should first look for "PCNF",
if not then "CNF" if not then "CRTD" if not then "REL". The status
field will never have PCNF CNF CRTD together.
Rama

 

[John Spencer]

Try this expression as a calculated field in your query. It is all on
one line, but I have split it into multiple lines for clarity.

IIF([SomeField] Like "*PCNF*","PCNF",
IIF([SomeField] Like"*CNF*","CNF",
IIF([SomeField] Like "*REL*",""REL",
IIF([SomeField] Like "*CRTD*","CRTD",,Null ))))

Insert that expression into a field "block" in your query (assumes you
are using Query design view).

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[rama]

Try this expression as a calculated field in your query.  It is all on
one line, but I have split it into multiple lines for clarity.

IIF([SomeField] Like "*PCNF*","PCNF",
     IIF([SomeField] Like"*CNF*","CNF",
     IIF([SomeField] Like "*REL*",""REL",
     IIF([SomeField] Like "*CRTD*","CRTD",,Null ))))

Insert that expression into a field "block" in your query (assumes you
are using Query design view).

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================

Hello
In my database there is a table called tblImport, which is imported to
database from MS Excel. This tblImport is having some fields called
"JobNo", "Status" etc. JobNo field contains unique data where as
Status field contains data which are separated by a space. What I wish
to do is to extract a substring from "STATUS" and get the result in
the final status. In excel with nested IF I can get the result as
below

STATUS                                     FINAL_STATUS
REL  PCNF NMAT PPRT PRC  SETC                PCNF
REL  NMAT PRC  SETC                         REL
REL  CNF  NMAT PRC  SETC                             CNF
REL  PRT  GMPS MACM PRC  SETC                REL
CRTD PRC                             CRTD

How to do it with the help of a access query.
Thanks in advance
Rama- Hide quoted text -

- Show quoted text -

Thank you very much Mr.John. Now my query is fine and brings out the
results as expected.
Rama