Find and Replace on Multiple Matches

[alexfwalker81]

I am working with allergen information, based on submissions from manufacturers. Believe it or not, manufacturers are sloppy and they don't do what they ought to, which is to bold, capitalise or underline allergens. So, I'd like to be able to (in layman's terms) look at the Long Text field which holds the ingredients list as supplied by the manufacturer. I'd like to be able to do a 'find and replace' on the allergens and replace the allergen as submitted with a capitalised (UPPER) version of the same word.

No problem, I can do that.

Except, I can't when I've got about 30 allergens in a table to match up... So, how can I search a string for any one of the values which are present in a table (or query), then replace the target values with upper case values?

 

[Minty]

I think you will have to loop through the table of allergens doing the UPPER conversion one search value(word) at a time.

You can't use a " LIKE IN ("Word1","Word2","Word3") ? because
a) that's a completely made-up syntax.
b) It wouldn't know which individual word to capitalise.

 

[theDBguy]

Just curious, have you tried using the Replace() function?

 

[alexfwalker81]

Just curious, have you tried using the Replace() function?

Yes, but I don't know how to tell the Replace() function to go and look at a list of values in a query or table.

Theoretically speaking, I could have a query with a column for every possible allergen, each with an individual Replace(), it's just not every elegant!

 

[theDBguy]

Yes, but I don't know how to tell the Replace() function to go and look at a list of values in a query or table.

Theoretically speaking, I could have a query with a column for every possible allergen, each with an individual Replace(), it's just not every elegant!

Hi. I just gave it a try, just to see if it's possible, and I didn't have to create a separate column for each allergen. All I had to do was use a non-equi join on both tables.

 

[Gasman]

Yes, but I don't know how to tell the Replace() function to go and look at a list of values in a query or table.

Theoretically speaking, I could have a query with a column for every possible allergen, each with an individual Replace(), it's just not every elegant!

PMFJI,
I would have thought you would have a record for each allergen?

 

[theDBguy]

I would have thought you would have a record for each allergen?

Great minds think alike. That's exactly what I did in my test. Hopefully, the OP has the same setup.

Result:

 

[alexfwalker81]

Great minds think alike. That's exactly what I did in my test. Hopefully, the OP has the same setup.

View attachment 84865

Result:

View attachment 84866

This is exactly what I'm after! How did you do it?

 

[Isaac]

@alexfwalker81
The string that needs the replacement (i.e., the string that contains allergen words which are still lowercase but need to be uppercase), that string has more in it than just the allergen word, right?

 

[alexfwalker81]

@alexfwalker81
The string that needs the replacement (i.e., the string that contains allergen words which are still lowercase but need to be uppercase), that string has more in it than just the allergen word, right?

Correct, the string is essentially the long bit of text that you'd see on the back of literally any packet of food, listing the ingredients.

 

[Isaac]

See attached - you'd need to edit the code in the module for your appropriate table, column names, etc.

 

[alexfwalker81]

See attached - you'd need to edit the code in the module for your appropriate table, column names, etc.

I've just tried this in my database and it works perfectly - thank you!

 

[Isaac]

I've just tried this in my database and it works perfectly - thank you!

Awesome, glad to hear it.
One thing to note is that on larger datasets (whether that be the allergen words table or the records that need correction), it is bound to become slower. If I have more time I will try to think of ways to make it faster or alternate approaches; right now I'm limited.
Good luck w/your project.

 

[Gasman]

My thoughts were to have the allergen word in lowercase, so as to be able to match with case for the replace.?
Then only select records that match with case the allergen word. So unless a new allergen is added to an existing record, the dataset to process would remain reasonably small each time, after initial run.?

 

[alexfwalker81]

Inevitably, I've just changed what I'd like to do, but can't figure out the syntax in the function to do it.

I now have two columns in the tblAlllergenWords; AllergenWord and AllergenWordReplacement. This is so that I can do clever things like find the word 'shrimp', matching on AllergenWord and replace it with 'SHRIMP (CRUSTACEAN)', which is in the field AllergenWordReplacement.

Like this: https://alexfwalker.tinytake.com/tt/NDYxNTQ0Ml8xNDYyOTg3Mw

However, when I do this and run the queries, an error occurs because I haven't adjusted the syntax correctly in the function: https://alexfwalker.tinytake.com/tt/NDYxNTQ0Nl8xNDYyOTg5MQ

Normally I can figure these things out, but not today it seems!

 

[arnelgp]

i added agpQuery (and on sub is my function).

 

[alexfwalker81]

Getting there; it seems to work, but then hits this error: https://alexfwalker.tinytake.com/tt/NDYxNTc1NV8xNDYzMDU5OA

I wonder if there's some kind of character in that ingredients list which is causing an issue?

 

[Gasman]

The single quote in cow's is causing the problem I would expect?

See if this corrects it, replace the line

strInput = Replace(strInput, "'", "''") & vbNullString

However it is arnelgp's function, so he will know it best?

 

[alexfwalker81]

Ok, makes sense. I can strip these out higher up the chain, so to speak which will then mean it runs properly.

 

[Gasman]

Ok, makes sense. I can strip these out higher up the chain, so to speak which will then mean it runs properly.

I'd do it in the function, so it always gets carried out in one place?