Replace a part string with another part string in a field using a table as the criteria lookup
Posted on 2014-03-04
I have a main table which contains thousands of records - tblPrograms. As there are many staff continually adding records to the table I end up with all sorts of variations for part strings of titles in the ProgramTitle field.
For example, the part string of "OneChannel" is sometimes input as "One Channel", "One_Channel" or "One-Channel" which makes querying the data difficult.
So, to try and assist in cleansing the data for reporting, I have created a table (TitleAdjustments) which has 2 columns - "PartStringEntered" and "PartStringReplaced". I want to add to this table as I find these types of inconsistences.
What I am hoping for is a function that basically finds the "PartStringEntered" in ProgramTitle field of tblPrograms table and only replaces that part of the title with "PartStringReplaced".
I do not want to permanently change the title in tblPrograms, only use this function in querying the data.
Is this possible?
Thanks in advance