Delete all data after a symbol in a field.

%3CLINGO-SUB%20id%3D%22lingo-sub-2275880%22%20slang%3D%22en-US%22%3EDelete%20all%20data%20after%20a%20symbol%20in%20a%20field.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2275880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22x-hidden-focus%22%3EHello%2C%20I%20have%20MS-Access%202007.%20In%20a%20Table%20field%20called%20ODOS%2C%20at%20the%20end%20of%20some%20entries%2C%20there%20is%20the%20symbol%20%22%26amp%3B%22%20and%20other%20words%20or%20numbers%20after%20that.%20I%20want%20the%20Symbol%20%22%26amp%3B%22%20of%20each%20entry%20and%20any%20letter%20or%20digit%20or%20word%20after%20that%20to%20be%20deleted.%20So%20if%20the%20entry%20is%20%22John%20Smith%20%26amp%3B%20123AB%22%20It%20must%20remain%20just%20%22John%20Smith%22.%20Can%20someone%20help%20me%3F%20(note%20that%20not%20all%20entries%20have%20the%20symbol%20%22%26amp%3B%22%20etc.%20entered.)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EDimitris%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2275880%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2368838%22%20slang%3D%22en-US%22%3ERe%3A%20Delete%20all%20data%20after%20a%20symbol%20in%20a%20field.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368838%22%20slang%3D%22en-US%22%3EMaybe%20an%20UPDATE-query%20like%20this%20works%3A%3CBR%20%2F%3E%3CBR%20%2F%3EUPDATE%20%26lt%3B%3CTABLE%3E%26gt%3B%3CBR%20%2F%3ESET%20ODOS%20%3D%20Left(ODOS%2C%20Instr(ODOS%2C%20%22%26amp%3B%22)%20-%201)%3CBR%20%2F%3EWHERE%20Instr(ODOS%2C%20%22%26amp%3B%22)%20%26gt%3B%200%3B%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
New 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;