Exact Match Query help

[katoc]

Hello.

I have a table containing the following:

Table 1
-------------
ID Title
-------------
1 Desk Procedures
2 Light Procedure, lamps
3 Bottom Procedures, Fixtures

I'm trying to do a query that uses a keyword entered by a user to do an exact match search. My query looks like this:

SELECT * FROM [Table 1] WHERE Title Like * [keyword] *

If the user enters "procedure", it returns all 3 records. I want it to return only record 2.

I tried to do
WHERE Instr(1, [Table 1].Title, keyword) > 0 but that also returns all 3.

-Sofia

 

[BNPMike]

As always with SQL, it is useful first to write your question in English. The thing is you are not asking for an exact match. Nor will you be satisfied with an inexact match.

I don't fully understand what you are looking for so Access stands no chance. It looks like you are saying "Select any record containing "procedure" but not containing "procedures"". If it is as simple as that then you can add that straight into your SQL, but maybe there are more situations you want to differentiate?

 

[melaniecarr23]

I understand what you are saying, but if you are trying to do and exact match, it's going to give you no results if you enter procedure because the actual field has more words in it than just the word procedure.

If you are wanting someone to be able to select a procedure, maybe do it by using a drop down box to be sure you get the results you want, OR suggest the user type in light or lamps to get the record you specify insead of procedure (which is used in each record). Sort of like keywords!

I hope this helps you some.

 

[katoc]

Yes, there are all sorts of situations and many different fields I will be searching. my example is a simplified one.

For example, If the keyword is "plan", I don't want access to return "Deer Park Plantation" or "Outside Plants", but I do want it to return "File Plan" or "Desk Plan Documents".

-Sofia

 

[melaniecarr23]

Could you have the user enter the word, and add a space at the end to it?

Title

criteria

Like ["keyword"]&" " - maybe there is a way to do that? I'm not that good with it, but that's what I'd be trying to work in (the space after the keyword so it's not counted as part of another word.

 

[lespaul]

In that case I would create a keyword table to search.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[katoc]

Okay, I tried using instr(1, [table 1].title, " [keyword]")>0 where I'm accounting for a space before the word. That works perfectly because grammatically, you'll have punctuation AFTER the word and the only time you'll have it before is if you're using quotation marks. So I figured it out myself. Thank you anyways.

Sofia