By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,017 Members | 602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,017 IT Pros & Developers. It's quick & easy.


P: 20
Is there a way that I can search whole words in a line rather than just search every letter.

ie when I am searching I will type AC (air conditioning) in a text box to search for only AC words but my code returns every two letters together that are ac therefore I will get "accept", "active" etc. but I only want AC as a whole word.

But I also want it anywhere in the line so if I have the line

"The AC is no longer active" I want it to return the record.

The code

Private Sub CmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtcause) Then
strWhere = strWhere & "([Cause] Like ""*" & Me.txtcause & "*"") AND "
End If

End sub
Aug 19 '08 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

There is no single answer to what you ask us regarding whole words - at least not without doing much more processing of the text than is involved with the "like *text*" comparison. You would need to extract complete words from the string and compare these to your search term instead of just matching the characters you type.

Why is this a problem? Well, if you ask yourself what defines a complete word in a string, you have to consider delimiters between words (which include spaces, numbers, special characters and punctuation marks such as ";,.@#~[]-?"and so on. It also includes the special cases of the word being at the beginning of a line (where no delimiter precedes the word) or at the end (no delimeter after the word). There is no automatic way to extract what you would consider to be a complete word from the string - VBA (like most computer languages) has no functions which can do this for you. You would need to write a custom function to extract the nearest whole word from the string and check whether it matches.

In my view the simplest approach is to accept that the current search will return all matching text, not just complete words. If you can live with that it avoids the considerable extra processing time involved in more precise pattern matching - not to say the effort of writing the custom functions. Using the simple search you already have you will find many (though by no means all) occurrences of complete words in a string just by adding a space before and/or after the word.

If you need to search multiple fields you will have to repeat the search clause for each of the fields you want to search and OR them together in your WHERE clause:

WHERE field1 Like ... OR field2 Like ... OR field3 Like ...

Aug 19 '08 #2

P: 50
It seems to me that if you add a space after the AC, then you will get only values that match that. No extra coding needed in that case.
Aug 19 '08 #3

P: 20
I will accept that it is probably not worth the effort.

Thanks for the replies

Aug 20 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.