Find and Replace on Multiple Matches (1 Viewer)

alexfwalker81

Member
Local time
Today, 10:52
Joined
Feb 26, 2016
Messages
90
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

AWF VIP
Local time
Today, 18:52
Joined
Jul 26, 2013
Messages
8,902
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

I’m here to help
Staff member
Local time
Today, 10:52
Joined
Oct 29, 2018
Messages
17,122
Just curious, have you tried using the Replace() function?
 

alexfwalker81

Member
Local time
Today, 10:52
Joined
Feb 26, 2016
Messages
90
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

I’m here to help
Staff member
Local time
Today, 10:52
Joined
Oct 29, 2018
Messages
17,122
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

Enthusiastic Amateur
Local time
Today, 18:52
Joined
Sep 21, 2011
Messages
9,284
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’m here to help
Staff member
Local time
Today, 10:52
Joined
Oct 29, 2018
Messages
17,122
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.

1599671269198.png


Result:

1599671302921.png
 

Isaac

Lifelong Learner
Local time
Today, 10:52
Joined
Mar 14, 2017
Messages
5,891
@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

Member
Local time
Today, 10:52
Joined
Feb 26, 2016
Messages
90
@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

Lifelong Learner
Local time
Today, 10:52
Joined
Mar 14, 2017
Messages
5,891
See attached - you'd need to edit the code in the module for your appropriate table, column names, etc.
 

Attachments

  • Testing 20200909.accdb
    552 KB · Views: 246

Isaac

Lifelong Learner
Local time
Today, 10:52
Joined
Mar 14, 2017
Messages
5,891
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

Enthusiastic Amateur
Local time
Today, 18:52
Joined
Sep 21, 2011
Messages
9,284
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

Member
Local time
Today, 10:52
Joined
Feb 26, 2016
Messages
90
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

..forever waiting... waiting for jellybean!
Local time
, 02:52
Joined
May 7, 2009
Messages
14,564
i added agpQuery (and on sub is my function).
 

Attachments

  • Testing 20200909.zip
    60.9 KB · Views: 171

Gasman

Enthusiastic Amateur
Local time
Today, 18:52
Joined
Sep 21, 2011
Messages
9,284
The single quote in cow's is causing the problem I would expect?

See if this corrects it, replace the line
Code:
strInput = Replace(strInput, "'", "''") & vbNullString

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

alexfwalker81

Member
Local time
Today, 10:52
Joined
Feb 26, 2016
Messages
90
Ok, makes sense. I can strip these out higher up the chain, so to speak which will then mean it runs properly.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:52
Joined
Sep 21, 2011
Messages
9,284
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?
 

Users who are viewing this thread

Top Bottom