Help with Operator like

[mgonzalez]

Hello


I am doing a query to databse access 2000 whith the operator like this is sentence

query = " SELECT f1,f2,f3 FROM DTI where name like ' * " & pname & " * ' "

I have readen other articles related whith this problem.Until I now this is the correct way to do the sentence but it doesn´t bring me anything .So someone may tell which could be the problem??

Thanks

 

[vasah20]

assuming that name is a field in the database, the problem might be coming from the whitespace.
it should look something like this:

query = " SELECT f1,f2,f3 FROM DTI where name like '*" & pname & "*' "

now you can find anything that has the value pname. Before, with the whitespace it looked for anything with _pname_ where _ is a space.

hope this helps.
leo

 

[chiph]

If you're going against Oracle, MS SQL Server or DB2, the wildcard operator is the percent sign (%), as these are ANSI SQL compliant databases. MS Access uses the asterisk (*), and is not ANSI SQL compliant.

You should be aware that using the LIKE operator results in a table-scan, which means that the database has to examine every row in the table, one at a time, to find the rows that meet your condition. This is very expensive.

If you're trying to give the user a way to find a name, you can use the SOUNDEX function (built into the database). This algorithm reduces a text string like a name to a 4 or 5 character string which is much easier to search for. If the user enters "SMITS" Soundex is likely to also show them "SMITH" and "SCHMIDT", so the user can pick the name that they misspelled.

The trick to using Soundex is you need another column in your table to hold the soundex values. Put a non-unique index on that column. Also, every time you insert or update the column the soundex is based on, you need to also update the soundex column. Eg:

UPDATE tbl_Customers
SET FName=?, LName=?, LNameSndx=SOUNDEX(?)
WHERE CustomerID=?

And you'd pass parameters of

"Joe", "Smith", "Smith", 23

When trying to find a customer by last name you'd do:

SELECT * FROM tbl_Customers
WHERE LNameSndx=SOUNDEX(?)

passing a parameter of what the user entered. You'll get back a list of customers with similar-sounding last names.

Hope this isn't too far off track on what you're trying to do.

Chip H.