InStr with Or?

Y

yator

I was using the following InStr() to extract data from a field. The string I
am extracting begins with “!TCANC†and ends with an ASCII Null.

I have now recognized that there is a variant that begins with “!OCANCâ€.

How would I modify my InStr to allow for the variant?

Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC"))
 
J

John Spencer

IIF(Instr(1,[TEXT],"!TCANC")> 0,
Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC")),
Mid([TEXT],(InStr([TEXT],"!OCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!OCANC")))

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

KARL DEWEY

Try this --
My_String: IIF(Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0)) Is
Null,
Mid([TEXT],(InStr([TEXT],"!OCANC")),(InStr([TEXT],Chr(0)),Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0)))
 
Y

yator

John what does the "1" in the first InStr refer to? i.e.; IIF(Instr(1,
thanx

John Spencer said:
IIF(Instr(1,[TEXT],"!TCANC")> 0,
Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC")),
Mid([TEXT],(InStr([TEXT],"!OCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!OCANC")))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I was using the following InStr() to extract data from a field. The string I
am extracting begins with “!TCANC†and ends with an ASCII Null.

I have now recognized that there is a variant that begins with “!OCANCâ€.

How would I modify my InStr to allow for the variant?

Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC"))
Click to expand...
Click to expand...
 
J

John Spencer

InStr has an optional argument as the first argument. It is the Starting
position of the search for a match. I usually include it, but it is not
needed. Look it up in the VBA help.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John what does the "1" in the first InStr refer to? i.e.; IIF(Instr(1,
thanx

John Spencer said:
IIF(Instr(1,[TEXT],"!TCANC")> 0,
Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC")),
Mid([TEXT],(InStr([TEXT],"!OCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!OCANC")))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
type mismatch error in macro in excel 0
How to extract portion of text 5
Find start position of the 9th occurance of a <character> in a str 2
Access 2007,reports,VBA 2
Extracting data from string 5
InStr Function not finding space 8
Making four queries into one 1

Top