Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Delete all data after a symbol in a field.

Copper Contributor

Hello, I have MS-Access 2007. In a Table field called ODOS, at the end of some entries, there is the symbol "&" and other words or numbers after that. I want the Symbol "&" of each entry and any letter or digit or word after that to be deleted. So if the entry is "John Smith & 123AB" It must remain just "John Smith". Can someone help me? (note that not all entries have the symbol "&" etc. entered.)
Thank you
Dimitris

1 Reply
Maybe an UPDATE-query like this works:

UPDATE <<table>>
SET ODOS = Left(ODOS, Instr(ODOS, "&") - 1)
WHERE Instr(ODOS, "&") > 0;