SQL in FP2003

[Ray]

I have used the following piece of SQL in a Database
Results wizard, but it doesn't work. What I'm trying to
do is get the user to enter a search string in the
field "search_name". But I don't expect them to put the
string in as an exact phrase, so that if they are looking
for "Bleak House" they would only have to type in "House"
and then "Bleak House" would be included in their results.
The URL I'm currently using is
http://www.cclweb.co.uk/hccdb/not_just_books/search_item.a
sp, and the SQL is as follows:

SELECT tblItem.ItemName, tblItem.Author,
tblItem.CodeNumber, tblItem.fkCondition,
tblItem.FkItemType, tblItem.FkItemCategory,
tblMember.Membership_Number, tblMember.First_Names,
tblMember.Last_Name
FROM tblMember INNER JOIN tblItem ON tblMember.Identifier
= tblItem.fkMember
WHERE (((tblItem.ItemName) like '%' & ::search_name::
& '%' ));

Any ideas?

 

[Jon Spivey]

Hi Ray,
you need to delimit the form field like this
'%::search_name::%'
so if somebody typed Bleak House FP would query
'%Bleak House%'

The full sql would be
SELECT I.ItemName, I.Author, I.CodeNumber, I.fkCondition,
I.FkItemType, I.FkItemCategory, M.Membership_Number, M.First_Names,
M.Last_Name
FROM tblMember M INNER JOIN tblItem I ON M.Identifier = I.fkMember
WHERE I.ItemName LIKE '%::search_name::%'

Jon
Microsoft MVP - FP

 

[Guest]

Jon
Thanks - I must have tried every combination but that one!