SQL statement will only search whole field 2

[sfolland]

I'm trying to do a keyword search from multiple fields that will return results from any field that contains a keyword (eg. "accounting" to find any record with the word accounting in it). I'm only getting results from fields that contain just the word "accounting" but not "accounting - handbooks, manuals. etc." Any help is appreciated, I'm relatively inexperienced with this.

I'm using the following statement.

SELECT *
FROM tblSingletonUrquhartLibraryCatalogue
WHERE [Enter Keyword] In ([txtAuthors],[txtTitle],[txtSubjects],[txtDescription])

 

[fredericofonseca]

you need to use the "like" keyword

e.g.
where my_field like '%accounting%' (or *accounting* depending on your DB)

will return records with accounting anywere on field my_field

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sfolland]

Thanks Frederico, but I want the search to be user-entered so I don't want to enter the search terms in the statement. I've tried using Like "*" in the statement but this returns the entire database.

Any suggestions?
Stuart.

 

[fredericofonseca]

Maybe I'm not understanding what you are trying to do.

You have a table with 3 field
Name
Address
Country

and you wish to search all records that contain "accounting" in any of the three fields

Is that it?

Or are you trying to allow your users to select which fields they are going to search for?

e.g.
one users wishes to search records where fields "name" and "address" contain "accounting", while other wishes to search only field "country"?

Regardless of what you are trying to do you will need to use the "LIKE" operator. How you use it is whats going to differ.





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sfolland]

I want users to enter any term (accounting as an example)that will search all fields. This I've done using the following statement:

SELECT *
FROM tblSingletonUrquhartLibraryCatalogue
WHERE [Enter Keyword] In ([txtAuthors],[txtTitle],[txtSubjects],[txtDescription])

The problem I'm having is that it only returns the records that contain the exact phrase typed in. So yes, I need the statement to search within the field and return all records that contain the word entered. I know that "Like" is necessary, but I have not been successful in including it in my statement.

Thanks,
Stuart.

 

[fredericofonseca]

No. I still don't understand.

Please give an example with real data, not with keywords, and include some records, and which ones are choosen by your given criteria.

And how do you wish then to enter your terms, and where.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dhookom]

Try this sql:
SELECT *
FROM tblSingletonUrquhartLibraryCatalogue
WHERE [txtAuthors] & [txtTitle] & [txtSubjects] & [txtDescription] Like "*" & [Enter Keyword] & "*";


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sfolland]

thank you & [thank.you];...I've been trying to resolve this one problem for 3 days, and your solution not only functions completely like I wanted it to but it also allows using * as a wildcard in the search, which is a totally unexpected suprise.

 

[dhookom]

Next time don't spend any more than 3 hours attempting to find a solution. Search Help, google, and these forums for an answer. If you don't find one, start a new thread.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sfolland]

I will! Again, thanks.

 

[fredericofonseca]

And next time give better examples of what you need (as was requested).

Just as an exercise for you, how would you do your SQL if you wished to allow the user to choose which fields to search?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[sfolland]

No offense Frederico, but I think I was pretty clear in my request (especially considering my experience, as I noted). I asked a very specific question in regards to a statement that I'd written (and posted) and your response did not really address it. While I appreciate your advice, I don't think your last post adds anything constructive to this thread. Although your suggested exercise may interest me at some point, I think we should consider this thread closed.

 

[fredericofonseca]

No offense taken, nor intended.


But going back to your original problem, and to the solution given by dhookom.

This solution is very similar to the one I would have given you, should you have supplied all requested information, but as solution it raises one particular problem, that may or not happen with your data.

Lets assume you are searching for "account present" on your fields.

And lets assume that you have one record with the following data.
txtAuthors = 15 char size field = " fixed account "
txtTitle = 20 char size field = "present today"
txtSubjects = 20 char size field = "subject 1"
txtDescription = 20 char size field = "description 1"

Even though NONE of the individual fields contains the string being searched the record will be returned by the query, as the string being searched will be
" fixed account present today subject1 description 1 " which meets the criteria.

but offcourse you are not interested on this as you have closed the thread.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dhookom]

Frederico,
The issue you pose is a good one. However, Access doesn't save trailing spaces in field values. The searched expression would be:
[tt][blue]fixed accountpresent todaysubject1description 1[/blue][/tt]
This would not match "account present".

However searching for "helpful" would match
txtTitle:"How to use Help"
txtSubjects:"Fully qualified individuals.."

So, your perceived issue is correct but your example is not (using JET/Access tables and queries).

To fix this issue, the expression might be:
[tt]WHERE [txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] & "~" & [txtDescription] Like "*" & [Enter Keyword] & "*";[/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sfolland]

Perhaps I closed the post too soon...

while this works:

SELECT *
FROM tblSingletonUrquhartLibraryCatalogue
WHERE [txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] & "~" & [txtDescription] Like "*" & [Enter Keyword] & "*";

- it only search one keyword. I've tried adapting it to make it search more than one user-entered keyword but no luck. Any ideas?

Stuart.

 

[dhookom]

We all seemed to have missed any suggestion of more than one key word. Your first posting stated "contains a keyword" which suggests only one.

You can either learn how to code changing the sql property of the query or,

WHERE [txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] & "~" & [txtDescription] Like "*" & [Enter Keyword] & "*" OR [txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] & "~" & [txtDescription] Like "*" & [Enter Another Keyword] & "*";

or create a table of key words and add the table to the query and use:
WHERE [txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] & "~" & [txtDescription] Like "*" & [tblKeyWords]![KeyWordField] & "*";

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sfolland]

Thanks Duane,

I've been talking to someone local who suggested writing the query like this :

[txtAuthors] & "~" & [txtTitle] & "~" & [txtSubjects] ...
[txtTitle] & "~" & [txtAuthors] & "~" & [txtSubjects]...

and joining the statements so that it would search using * between the keywords. Currently it will search this way but only in a forward direction. I will test yours when I'm home but I see how it should work.

And yes, the idea of multiple keywords didn't actually occur to me until yesterday when I decided to test everything.