Truncate String With "(", ")" Delimeter?

[Ramya_mudambi]

Hi All,
In Access 2007, in a table “Source” -> "UserName" column, I'm trying to truncate and extract value in the string:
a. Value within the parenthesis preceded by "\".
b. Value outside the parenthesis preceeded by “\”

“Source” table contains the column ‘UserName’
“Final” table contains the columns ‘UserName’, “Expected_Output_1” and “Expected_Output_2”.
I tried using the following expressions to extract the output results, but unfortunately there’re not displaying the results in correct format.

For extracting results of “Expected_Output_1” I used: Mid([UserName],InStrRev([UserName],"\")+1)

For extracting results of “Expected_Output_2” I used:
Mid(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),InStrRev(Mid([UserName],InStr([UserName],"(")+1,InStr([UserName],")")-InStr([UserName],"(")-1),"\")+1)

Attaching the database for your reference, someone please guide me and help me fix this.

Expected results should match the snapshot attached or table “Final” in the attached DB

Example:
UserName = \\C103\E000001 then the “Expected_Output_1” = E000001, “Expected_Output_2” = nothing

UserName = NAHoM\_INTRA_MTS (NAHoM\DNAGAIntranetAdmin) then the “Expected_Output_1” = _INTRA_MTS, “Expected_Output_2” = DNAGAIntranetAdmin

Many thanks,

 

[namliam]

I would probably write some VBA for this, trying to do this with an inline function would seem possible, but will get messy IMHO.

WRiting the VBA, though the execution may be slower, will make it more maintainable and probably easier to (de)bug