jacobe2008 said:
Hello...
I have the following two fields:
Field1 Field2
red The apple is
green I love grapes
I want to produce this new field:
Field 3
The apple is red
I love green grapes
How do I produce Field3?
(Note my actual table has about 300 of records. Also note that
Field2 is not consistent).
Automatically?? No hope in hell, especially if the data in Field2 is not
consistent.
I suppose, and this is entirely off the top of my head, you could add a
table to the database containing a list of words to be modified (I'm
assuming that Field1 contains modifiers that are intended to be used to
modify selected words in Field2). That would be a minimum starting
point.
Then I am thinking that you will need a VBA function to loop through a
submitted string (another assumption: Field2 might contain multiple
keywords to be modified) and insert the supplied modifier before each
keyword in the string. A simple call to the builtin Replace function
won't work because Field2 might contain two or more different keywords,
correct?
You could name the function "AddModifier" and define it to take two
arguments, sToBeModified and sModifier, and return the modified string.
It would do these steps:
Open a shapshot recordset on the table containing the list of keywords
Use split() to create an array containing each of the words in the
sToBeModified string
Loop through the array, looking up each word in the recordset.
If not found, add the word (along with a space) to the string variable
defined to contain the string that will be returned to the caller.
If found, add the sModifier value to the string before adding the word
to the return string
Once that is done, you can create a query that calls the function, like
this:
Select Field1, Field2, AddModifier(Field1, Field2) As ModifiedString
....
