Find or Search Function in Excel not able to work in Access Query? 1

[Kirkebbs]

I am trying to extract characters from a string of text. The location of the characters is not always in the same position, however they are always surrounded by "__" characters. In excel I am able to use the MID and FIND functions to make it work. Example... the string looks like this "MONITORS__DIFF__W_3178" I want to extract the characters "_DIFF" from the string. My formula in excel is =IF(ISERR(MID(A2,FIND("__",A2)+1,IF(ISERR(FIND("__",A2,FIND("__",A2)+1)), FIND("__",A2),FIND("__",A2,FIND("__",A2)+1))-FIND("__",A2)-1)),"",MID(A2,FIND("__",A2)+ 1,IF(ISERR(FIND("__",A2,FIND("__",A2)+1)),FIND("__",A2),FIND("__",A2,FIND("__",A2)+1))-FIND("__",A2)-1))

I need to use Access because the data exceeds 65,536 rows. Any suggestions?... Thanks Kirk

 

[SkipVought]

Hi,

instead of FIND, use the instr function.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PHV]

You may try this:
Left(Mid(yourVariable,InStr(yourVariable,"__")+1),InStr(Mid(yourVariable,InStr(yourVariable,"__")+1),"__")-1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886