If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Finding text within a string on a query

I am trying to find certain pieces of text in a string field in a query. Say i have:

red, blue, green, yellow
blue, green, black
yellow, red, white

I want to be able to extract all the records that have "red" in them regardless of where it falls in the field. I have a query set up but have no idea what the criteria should be. In excel i could have used a find or search funtion, but not sure what those are in access. I am not confident in VB so would like the formula that should be put in the criteria field if possible.

depending on how many key words you are looking for I'd try
select my,column,list from mytable
where
mycolumn like "* red *" or mycolumn like "* red,*" or mycolumn like "* red.*"
or
mycolumn like "* green * " or mycolumn like "* green,*" or mycolumn like "* green.*"

the like "* red *" should find any occurance of red wiith spaces either side (so it would find red, but not tred or redness)
the like "* red,*" should find any occurance of red with a comma after it, as before it will find red, but not tarred,
the like "* red.*" as above but with a full stop
it would be easier if Access handles regular expressions, it may even make sense to write a dll class that wraps the .net regexp class so it can be used in Access, or of course you could use are old mucker Google and see if someelse has already done it.

I think you may well have a significiant performance problem with this though, especially if the number of rows in the table (or filtered as part of the where clause) expands. If it were me I'd want to shift this sort of processing into a server based db. I know MySQL has the fulltext index which would be a breeze for this.

what you could consider is a redesign of the table so that keywords are parsed already and the SQL engine can be much more efficient that trying to do a table scan, looking for multiple instances of a single or multiple word.

you may also need to bear in mind capitalisation RED isn't the same as Red or even red.

I supose it depends on how many key words you have and how often they change or get added

That looks interesting and tried to use it, but unsure what you mean by a WHERE clause. I put the forumla in the criteria section on a query design view but it didn't work. i used

=InStr(0,[colours],"red")>0

That makes sense to me (or it would in something like excel) as Instr returns a numeric position of the text, but it doesn't work. Any further pushes in the right direction?

healdem: you lost me completely, although i liked the jist of what you were saying. On searching around the internet i did read several things about performance on things like this. do you think i should look at filters instead?

if you are using the query builder then you need to do things differently. the query builder acts as a shield between the user and SQL. as you get on developing stuff in Access you'll probably start using SQL. if you want to have a look at SQL (IN A2003/XP) under the file menu item there is a button with a grid symbol, click that button and select the SQL view

however in the query builder just put the terms you want in the criteria boxes under the relevant column

eg under colours type
like "* red,*"
like "* red.*"
like "* red *"
like "red*"

that should retrieve any row with colours containing red. you may be able to simplify it if you know there is no punctuation (ie you don't have to look for the comma or fullstop), you may have to complicate it if you know there are other punctuation used.

the main problem in my books is that you don't know if the word you are looking for appears nly inthe form you want (eg red) or appears in seeral forms (eg red, reddish brown etc)
you don't know if there is more than one colour per entry. if there is only one colour per entry then ="red" will work, if there is no punctuation then you could look for "red", "red *" and "* red*"

if you are constraining the colours column to only contain colours you may be better of redesigning the table, so that the colour(s) uses become a sub type so you are only looking for a single element each time. if you want to look for garments say with blue, white or red in starts to get very complex very quickly.

if you had a table with colours in, and a sub table which associates colour with product you then have the ability to easily identify as many colours as required in a clear unambiguous way.