Convert Excel formula to Access

[Ozzie via AccessMonster.com]

Guys,

I need to convert the following excel formula into Access

=PROPER(LEFT(MID(A19,FIND("CN=",A19)+3,255),FIND("/OU=GB",MID(A19,FIND("CN=",
A19)+3,255))-1))

Can anyone help as I am having a 'thick' moment!, it would be really
appreciated,

Thanks

Ozzie

 

[Guest]

Try...

strconv(Left(Mid(A19,InStr(A19,"CN=")+3,255),InStr(Mid(A19,InStr(A19,"CN=")+3,255),"/OU=GB")-1),3)

Note that InStr replaces Find and its parameters are reversed.
Also, I have left "A19" in the expression although this will need to be
replaced with the field name you wish to convert.

Regards

Andy Hull

 

[John Spencer]

You will need the following functions
StrConv - Convert string to proper case
Instr - Find a string within a string
Left - return a portion of a string
Mid - return a portion of a string

Beyond that it may be better if you could give an example of your field and
what the result you want. Along with the rules you are using to get that
result

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

 

[Ozzie via AccessMonster.com]

Hi,

I have a field that varies in length but always has the following format;

'CN=Alan Painter/OU=GBSRD02/OU=TDE/O=AREVA-TD,CN=Ca'

the piece that I am attempting to extract is the persons name at the start,
which always comes after the CN= and before the '/OU=GB'

Apologies for delaying coming back only work internet has been playing up!!

Many thanks

Try...

strconv(Left(Mid(A19,InStr(A19,"CN=")+3,255),InStr(Mid(A19,InStr(A19,"CN=")+3,255),"/OU=GB")-1),3)

Note that InStr replaces Find and its parameters are reversed.
Also, I have left "A19" in the expression although this will need to be
replaced with the field name you wish to convert.

Regards

Andy Hull

Guys,

[quoted text clipped - 9 lines]

Ozzie

 

[Guest]

Hi again

The formula in my previous post should work

Andy Hull

Hi,

I have a field that varies in length but always has the following format;

'CN=Alan Painter/OU=GBSRD02/OU=TDE/O=AREVA-TD,CN=Ca'

the piece that I am attempting to extract is the persons name at the start,
which always comes after the CN= and before the '/OU=GB'

Apologies for delaying coming back only work internet has been playing up!!

Many thanks

Try...

strconv(Left(Mid(A19,InStr(A19,"CN=")+3,255),InStr(Mid(A19,InStr(A19,"CN=")+3,255),"/OU=GB")-1),3)

Note that InStr replaces Find and its parameters are reversed.
Also, I have left "A19" in the expression although this will need to be
replaced with the field name you wish to convert.

Regards

Andy Hull

Guys,

[quoted text clipped - 9 lines]

Ozzie

 

[John Spencer]

Mid(Left(strParse,Instr(1,strParse,"/OU=")-1),Instr(1,StrParse,"CN=")+3)

And if the string always starts with "CN=", you could simplify that to
Mid(Left(strParse,Instr(1,strParse,"/OU=")-1),4)


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

Hi,

I have a field that varies in length but always has the following format;

'CN=Alan Painter/OU=GBSRD02/OU=TDE/O=AREVA-TD,CN=Ca'

the piece that I am attempting to extract is the persons name at the
start,
which always comes after the CN= and before the '/OU=GB'

Apologies for delaying coming back only work internet has been playing
up!!

Many thanks

Try...

strconv(Left(Mid(A19,InStr(A19,"CN=")+3,255),InStr(Mid(A19,InStr(A19,"CN=")+3,255),"/OU=GB")-1),3)

Note that InStr replaces Find and its parameters are reversed.
Also, I have left "A19" in the expression although this will need to be
replaced with the field name you wish to convert.

Regards

Andy Hull

Guys,

[quoted text clipped - 9 lines]

Ozzie