Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...
MS Access Forum / Queries / May 2008

Tip: Looking for answers? Try searching our database.

InStr with Or?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
yator - 08 May 2008 19:43 GMT
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"))
Reply to this Message
John Spencer - 08 May 2008 20:54 GMT
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.
[quoted text clipped - 4 lines]
>
> Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC"))
Reply to this Message
yator - 09 May 2008 17:04 GMT
John what does the "1" in the first InStr refer to? i.e.; IIF(Instr(1,
thanx

> IIF(Instr(1,[TEXT],"!TCANC")> 0,
> Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC")),
[quoted text clipped - 13 lines]
> >
> > Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC"))
Reply to this Message
John Spencer - 09 May 2008 18:19 GMT
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
[quoted text clipped - 7 lines]
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
Reply to this Message
KARL DEWEY - 08 May 2008 20:55 GMT
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)))

Signature

KARL DEWEY
Build a little - Test a little

> 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.
[quoted text clipped - 4 lines]
>
> Mid([TEXT],(InStr([TEXT],"!TCANC")),(InStr([TEXT],Chr(0))-InStr([TEXT],"!TCANC"))
Reply to this Message

 Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.