Problem with SQL statement

[patbb]

I have an access database where I have a field called Description. This field contains all the description about a particular computer for example computer1 hard disk 10 GB, memory 64 MB.....

Now I would like to write a SQL Statement which will retrieve all records which contain the word 'Hard disk' in their Description field. Note that not all records will have the word 'Hard Disk'.


I have tried the following SQL statements but they do not work

first method:

select * from CompTable where Description in ("Hard Disk")

This method search for records where the Description field has only the word 'Hard disk' and no other words


Second Method:

select * from CompTable where Description like '%Hard Disk%'

The second method is better but Can I search 2 or more Keywords at the same time in the same SQL statement?


Are there any way where I can perform the desired query?


thanks

[colin]

You could try something like:

select * from CompTable where Description like '%Hard Disk%' or '%hard disk%' or '%Hard%' or '%Disk%' etc....

"OR" allows you to use options within your query but depending on how the database is structured the queries can become very slow to run. You could try adding additional fields instead of a description box i.e. check box options which would make it far easier to search or even text boxes limited with input masks to keep the database efficient. Large text fields can become inefficient and free text searches can be unreliable.

Hope this helps

Colin

[rickm]

Originally posted by patbb
I have an access database where I have a field called Description. This field contains all the description about a particular computer for example computer1 hard disk 10 GB, memory 64 MB.....

Now I would like to write a SQL Statement which will retrieve all records which contain the word 'Hard disk' in their Description field. Note that not all records will have the word 'Hard Disk'.


I have tried the following SQL statements but they do not work

first method:

select * from CompTable where Description in ("Hard Disk")

This method search for records where the Description field has only the word 'Hard disk' and no other words


Second Method:

select * from CompTable where Description like '%Hard Disk%'

The second method is better but Can I search 2 or more Keywords at the same time in the same SQL statement?


Are there any way where I can perform the desired query?


thanks

well, you could say select * from CompTable where Description like "HardDisk" or Description like "Floppy" or...

or, if you want to have a list of words, you could say

where "String of one or more search items" like '%' + Description + '%'

You will have to format it for access if that is not correct, but that is how it would work in SQL server. The other thing is that you would have to have the entire description in the search string.

Hope it helps