Search

[helpmeplease213]

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

[Stewart Ross]

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 ...

-Stewart

[Krandor]

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.

[helpmeplease213]

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

Thanks for the replies

regards