CONTAINS with *

Hi,
I read in MSDN that
If the text and asterisk are not delimited by double quotation marks, as in
CONTAINS (column, 'text*'), full-text search considers the asterisk as a
character and will search for exact matches to text*.
However... I have this code:
If chkOptions.Items(1).Selected = True Then 'Starts With...
strText = "" & strText & "*" & ""
End If
If chkOptions.Items(0).Selected = True Then 'Allow Flexionary Forms...
strText = "FormsOf(INFLECTIONAL," & strText & ")"
End If
strSearch = "SELECT " & _
"ST.[Rank], Titles.title as Title, Titles.notes as Notes, Authors.au_fname
as [F.Name], Authors.au_lname as [L.Name] " & _
"FROM " & _
"ContainsTable (Titles, *, '" & strText & "') as ST " & _
"INNER JOIN Titles ON ST.[Key] = Titles.title_id " & _
"INNER JOIN TitleAuthor ON Titles.title_id = TitleAuthor.title_id " & _
"INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id " & _
"ORDER BY ST.[Rank] DESC"
I enter the string cook as strText. Then I build strText as "cook*"
(containing the double quotes).
For some reason, which I have yet to understand, VB takes away my double
quotes, so in the end strSearch comes out as:
SELECT ST.[Rank], Titles.title as Title, Titles.notes as Notes,
Authors.au_fname as [F.Name], Authors.au_lname as [L.Name] FROM
ContainsTable(Titles, *, 'cook*') as ST INNER JOIN Titles ON ST.[Key] =
Titles.title_id INNER JOIN TitleAuthor ON Titles.title_id =
TitleAuthor.title_id INNER JOIN Authors ON TitleAuthor.au_id = Authors.au_id
ORDER BY ST.[Rank] DESC
Anyway, my question is this - since as per MSDN the engine searches for the
string 'cook*' (containing the asterisc), it should NOT find any (I have no
string 'cook*' in my table). However, it returns the exact same result as if
I only searched for 'cook', without any asterisc - I get back two records.
The "INFLECTIONAL" part, though,works just fine.
I think I should go to bed now - apparently I can't see something obvious
;-)))
Thank you.
Alex.
"msnews.microsoft.com" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in
message news:%234z9qZEBFHA.1084@.tk2msftngp13.phx.gbl...
> strText = "" & strText & "*" & ""

> I enter the string cook as strText. Then I build strText as "cook*"
> (containing the double quotes).
> For some reason, which I have yet to understand, VB takes away my double
> quotes, so in the end strSearch comes out as:
Change the above line to
strText = """" & strText & "*" & """"
A "" by itself is an empty string (you have the double quotes to enclose a
string, and there's nothing between them). A """" tells VB you want a
literal " in a string (you need 2 of them as VB would treat a single one as
the end of a string enclosure and then error because it thinks you have
another opening string quote after it).

> Anyway, my question is this - since as per MSDN the engine searches for
> the string 'cook*' (containing the asterisc), it should NOT find any (I
> have no string 'cook*' in my table). However, it returns the exact same
> result as if I only searched for 'cook', without any asterisc - I get back
> two records.
Because you have not enclosed the cook* in double quotes (see above for how
to fix this) the * is being treated as a wildcard, hence cook is a match.
With the change to the one line above, it should work as you expect.
Dan