Update replace query for characters with bracket

[shermund]

hi guys,

i have a list of names with came with some random characters in a bracket, which is not fixed numbers of characters, it could range from 4 to 10. etc

Name

James bond (MY0111)
Bonnie James (Taman Cheers)


i am trying to make a update query that removes the characters including the bracket so the result will be

James Bond
Bonnie James


tried using wildcard in the function but not working.

replace(raw.[names], "(*)", "")

but this returns with no changes.


Please help me with this as my project is dueing tomorrow and i have yet to find a way to work around this.




regards

Shermund

 

[SteveSchapel]

Shermund,

Does this do the trick?...

Left([Names],InStr([Names],"(")-2)

 

[namliam]

Asumming this will not happen:
James (MY0111) Bond

You can use something like:
left(YourField, instr(1, Yourfield,"(") - 1)

If above sample does happen it gets much more complex, but doable.

Now if
James (MY0111) Bond (MY0111)
or
James (MY0111 Bond
or something simularly odd happens, that is going to cause yet more trouble.

 

[shermund]

Hi guys, thank you for the quick reply. So i tried both code and both gave me error.

For Steve's code, it didnt change anything.

for namliam's code, it wipes out the entire field. but at times it works, though it left a space right behind the name but i think that can be solve quite easily.


in this case i am assuming that replace function is not suitable, right?

 

[SteveSchapel]

Shermund,

Where you say "it didnt change anything", can you please define "it"? In other words, where and how are you trying to apply the suggestions given? If a query, please post the SQL view of the query. If in VBA code, please post the exact code. Thanks.

 

[shermund]

Thank you for the quick reply, sorry i noted the wrong results.

this is the sql code:


UPDATE raw SET raw.name = Left([Name],InStr([Name],"(")-2);


Your code appear to work perfectly fine, only if i added the criteria of "*(*)" because some of the data need not changing. or it would wipe out the correct names as in.


Original data:
Name
James Bond
James Blond (MY0550)
Jamie Bond (taman cheras)

result without the criteria:
Name
*Emptied field
James Blond
Jamie Bond

so the final code is,


UPDATE raw SET raw.name = Left([Name],InStr([Name],"(")-2)
WHERE (((raw.name) Like "*(*)"));


so far it seems to be quite promising! you just made my day, Mr. Steve.
is there anyway to make this code better?


regards

Shermund

 

[SteveSchapel]

Shermund,

First point is that "name" is a Reserved Word (i.e. has a special meaning) in Access, and as such should not be used as the name of a field or control. In fact, I am surprised that raw.name in your SQL will produce the correct results. I recommend you to change this if you can.

Yes, my suggested expression did assume that all records had the parentheses segment in the data. Your solution of putting the WHERE is as good as any way to handle it.

 

[shermund]

Thank you for your advise, sir. I shall change that field into something else. you've been great help.

 

[SteveSchapel]

I'm happy to know we have made progress here, Shermund. Best wishes with the rest of your database project.