How to search a string

[jamesnkk]

How do I search a string -
I have a ms access table consist of 2 columns -

"Part Nos"........... "Components "
K123456........ ..... U123,Q545,U5,Q3 321,U22
P456789........ ......U13,U18,Q 123,P555,X222

User enter a component number such as U5 and I must be able to retrieve
the Part Nos -K123456. How do I search thru the string in the "Components " column which is separate by comma.

[debasisdas]

You have to use LIKE search in database.

[CyberSoftHari]

You can not split and search instead you can use Like %string%
(i.e)
[CODE=sql]Select * from tblTableName where SearchField like %searchString%[/CODE]

[jamesnkk]

You can not split and search instead you can use Like %string%
(i.e)
[CODE=sql]Select * from tblTableName where SearchField like %searchString%[/CODE]

thanks you so much for the code

[devonknows]

Just thought i would offer a few modifications here if not for you then for others that would like to know.

Im assuming searchString is a variable (Which it would be if you was entering the search string into a textbox of some sorts), if so then the sql statement is not valid, beacuse it will just search the table for the word 'searchstring'.

This is the Old One.

• Sql = "Select * from tblTableName where SearchField like %searchString%"

This is What it Should be.

• Sql = "Select * from tblTableName where SearchField like '%" & SearchString & "%'"

Also, If your SearchString is more than one word like a two word phrase or something and you want to search for all instances of these words instead of the phrase as a whole then try something like this.

• Sql = "Select * from tblTableName where SearchField like '%" & Replace(SearchString, chr(32), chr(37)) & "%'"

What this does, it is replaces spaces (Chr(32)) with percentages (Chr(37)). if there is a percentage in the phrase it will be queried as a wildcard and will return all instances before the % and after the % or between multiple %.

Hope this is a little more insight
Kind Regards
Devon.

[CyberSoftHari]

Most welcome for your valuable reply.