Separate data into two fields

[esskaykay]

I have a database with a field LNAME with last names preceded by maiden names in parentheses. I would like a query that would update fields (LAST and MAIDEN) with the corresponding data:

Example –
LNAME = (Jones) SMITH
LAST = SMITH
MAIDEN = Jones

Any ideas would be greatly appreciated.

Thanks,
SKK

 

[Alc]

Assuming the format is always the same i.e. "(" then maiden name then ") " then first name, you could split it up as follows:

str_WholeName = "(Jones) SMITH"
'--- set this somehow

li_End_Bracket = Instr(str_WholeName,")")
'--- gives the position of the 'closing' bracket within the string, in this case 7

str_Maiden= Left(str_WholeName,li_End_Bracket-1)
'--- gives "(Jones" (first six characters)

str_Maiden= Right(str_Maiden, Len(str_Maiden)-1)
'--- gives "Jones"

str_Last = Right(str_WholeName,Len(str_WholeName)-(li_End_Bracket+2))
'--- gives "SMITH" (whole name, from two characters after the location of the end bracket)

May need tweaking a bit, as I was in a rush, but hope it helps.

 

[esskaykay]

Thanks Alc, will do...