Microsoft Access database to exclude "Wire" and "Fire" from a text field

donnie91910
donnie91910 used Ask the Experts™
on
I have an Microsoft Access database table where I am trying to exclude "Wire" and "Fire" from a memo text field called Description.  I want everything to show up that does not have the word "Wire" and "Fire" in it.
Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
You can use the Replace() function in a query or in your VBA event code (in case you're using an unbound control).

Example:
Replace(Replace("Wire", ""), "Fire", "")

Open in new window

Author

Commented:
so how do I use the "Replace" function in my query?  The table name is Meter and the field name is Description.
Top Expert 2014

Commented:
Select Replace(Replace(meter.description, "Wire", ""), "Fire", "") As CleanDesc
From Meter

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
Where YourField Not Like "*Wire*" and  YourField Not like "*Fire*"

Keep in mind that this will also eliminate anything with "WireCutter" or "Fireplace" or "Fireman" so you might want to use a more sophisticated pattern match than just leading and trailing *'s.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I wrote an article on complex text searches a few years ago, you might get some ideas from it.

Easiest method:
SELECT [yourField] from yourTable
WHERE [yourField] NOT LIKE "*[ .,]Wire[ .,?!]*"
AND [yourField] NOT LIKE "*[ .,]Fire[ .,?!]*"

Open in new window

Note that I've included a couple of possible options for character which might preceed or follow those terms, so this is a little more detailed than Pat's recommendation and would not include those records where "Fire" or "wire" is part of a larger word.
John TsioumprisSoftware & Systems Engineer

Commented:
I am under the impression that you want to exclude "lines" from the Memo Field
For this you need to find the new line characters..put them in an array (split) and work your way on removing the offending line

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial