Extract Country Code from a larger field

[wcrimi]

I have a field called "comment" that will contain some information including a country code. The total length of the comment will vary. The country code can be 2 or 3 positions, will always be at the end of the comments field, and will always be enclosed inside parenthesis. I was to extract the 2 or 3 position country code from that comment field.



Here are some examples:

Package(FR) I want FR extracted from that into another field

Package(GB) I want GB extracted from that into another field

Letter(IRE) IRE

Fax(UAE) UAE

Thanks

[ItsMe]

Something like this should do the trick.

Dim strFile As String
strFile = "Package(GB)foo(f)d"
strFile = Left(strFile, InStr(strFile, ")") - 1)   'Trim everything to the right of first Parenthesis
strFile = Mid(strFile, InStr(strFile, "(") + 1)   'Trim everything to the Left of first Parenthesis
MsgBox strFile

[wcrimi]

Is there any way to do this within one statement?

This is a field inside a table that's already part of a query and I just want to extract the country inside one of the columns.

[CJ_London]

try this in a query

Countrycode:strrev(mid(strrev([Comment]),2,instr(strrev([Comment]),")")-2))

[June7]

strrev should probably be InStrRev

[CJ_London]

strrev should probably be InStrRev

no - reversed the comment in case there happened to be another bracket in the comment

but could also do

Countrycode:left(mid([comment],instrrev([Comment],"(")),len(mid([comment],instrrev([Comment],"(")))-1)

or

Countrycode:replace(mid([comment],instrrev([Comment],"(")),")","")

which is probably simplest

[June7]

StrRev is not valid function in VBA immediate window for my test.

This works. x represents the field.

Left(Mid(x,InStr(x,"(")+1), Len(Mid(x,InStr(x,"(")+1))-1)

EDIT: agree, Replace simplest

[wcrimi]

try this in a query

Countrycode:strrev(mid(strrev([Comment]),2,instr(strrev([Comment]),")")-2))

This got an undefined function. strrev

[wcrimi]

StrRev is not valid function in VBA immediate window for my test.

This works. x represents the field.

Left(Mid(x,InStr(x,"(")+1), Len(Mid(x,InStr(x,"(")+1))-1)

EDIT: agree, Replace simplest

We have a WINNER!

Thank you!

[CJ_London]

StrRev is not valid function in VBA immediate window for my test.

Ah yes - me thinking in shorthand - should be strReverse