|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 8th, 2008, 07:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
InStr with Or?
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")) 
|
|
#2
May 8th, 2008, 08:54 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
InStr with Or?
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 yator wrote: 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"))
|
|
#3
May 8th, 2008, 08:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
InStr with Or?
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))) -- KARL DEWEY Build a little - Test a little "yator" wrote: 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"))
|
|
#4
May 9th, 2008, 05:04 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
InStr with Or?
John what does the "1" in the first InStr refer to? i.e.; IIF(Instr(1,
thanx "John Spencer" wrote: 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 yator wrote: 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"))
|
|
#5
May 9th, 2008, 06:19 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
InStr with Or?
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 yator wrote: John what does the "1" in the first InStr refer to? i.e.; IIF(Instr(1, thanx "John Spencer" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
