How can I remove parenthesis and everything that comes after it? 1

[accesshelp1]

I have a query that shows IDs with a parenthesis and another ID. I only want the first ID and to remove the ID within the parenthesis.
For example an ID looks like this: 12345 (54321). I would like to have the data return 12345 and completely drop the parenthesis and everything that comes after it and I can't figure out how to do this.

The biggest challenge here is not all IDs are the same length so I can't just use a TRIM.

I tried Replace ( Replace(Replace([ID],"(",""),")","")) but that added a space in between the two IDs, meaning ONLY the parentheses was removed and the ID ended up looking like this 12345 54321.

Does anyone know how to remove everything that would come past the parenthesis?

Thanks

 

[dhookom]

Something like this should work:

Trim(Left([ID],Instr([ID]&"(","(")-1))

If there is no "(", it would add one to avoid an error.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

You can also do:
[tt]
Update YourTableName
Set ID = [blue]Trim(Split(ID, "(")(0))[/blue]
Where InStr(ID, "(") > 0[/tt]

Welcome to Tek-Tips

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

>I tried Replace ( Replace(Replace([ID],"(",""),")","")) but that added a space in between the two IDs
Well, that did not add a Space, you already did have a Space in there, right?

You could, instead of detecting a "(", you could go after the Space between ID's, ignoring the "("
[tt]
Update YourTableName
Set ID = Trim(Split(ID, " ")(0))
Where InStr(ID, " ") > 0[/tt]

or
[tt]
Update YourTableName
Set ID = Left(ID, InStr(ID, " ") -1)
Where InStr(ID, " ") > 0[/tt]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson