[a-z] wildcard search

[katoc]

Hello.

I'm trying to write a SQL statement to be used in VBA that will search for a keyword and make sure there are no alphanumeric characters immediately following the keyword. For example:
"Business Planning Forms" -> should not be a result
"Draft Plan Sheet" -> should be a result
"Dock Plan, Boat Materials" -> should be a result

My current SQL query is the following:

SELECT * from [file] where [file].[Title] like "*plan[!a-zA-Z0-9] *";

However this does NOT work. Can someone tell me why and how to make it work?

I also tried -> like "*[!a-zA-Z0-9]plan[!a-zA-Z0-9]*"

-Sofia

 

[RoyVidar]

Both of your samples work on my setup (except, for the first one, I removed the additional space at the end), what are your actual results? That's much more interesting with regards to troubleshooting, than the more laconic "However this does NOT work".

Another way, would be invoking some code, cause the above would have the "weakness" of not being able to find anything unless there's a non alpha after (or both in front of and after) the criterion, and wuold not find where the criterion is at the start of the field or the end of the field.

For such, I think perhaps some code might be needed, perhaps Regular Expressions?

Here's a go at it/starting point - place the following function in a standard module

[tt]' In the declaration section of it
private gre as object

Public Function MatchWord(byval v_varIn as variant, v_strSearch as string) as boolean

if (len(v_var)>0) then
if (gre is nothing) then
set gre = createobject("vbscript.regexp")
with gre
.multiline = true
.ignorecase = true
.pattern = "\b" & v_strSearch & "\b"
end with
end if
MatchWord = gre.test(v_varIn)
end if

end function[/tt]

Call it with

[tt]...where MatchWord([file].[Title],"plan") = True[/tt]

Roy-Vidar

 

[katoc]

Thanks Roy-Vidar, your reply gave me some clues into what I was doing wrong.

When I said 'this does not work', I meant I wasn't getting any results. Should have mentioned that I guess. The problem was that in my table, the word "plan" was appearing at the end of the string which meant there was nothing after it. So I've changed my sql query to:

SELECT * FROM [file] WHERE
[Title] Like "* plan[!a-zA-Z0-9]*" OR
Right([Title], Len("plan")) = "plan";

and this works beautifully. It returns:
"Desk Plan" and "Book Plan for library" AND "chair plan, library book"

So thank you very much.

-Sofia