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.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Finding text within a string on a query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 9
Question 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.

Hopefully this is possible!!

Thanks
Reply With Quote
  #2 (permalink)  
Old
Computer Monkey
 
Join Date: May 2005
Posts: 1,191
Have a look at using the InStr Function in a WHERE clause.
__________________
Me.Geek = True
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,206
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
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 9
Quote:
Originally Posted by nckdryr
Have a look at using the InStr Function in a WHERE clause.
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?

Thanks for the quick replies. Access is making me pull my hair out.
Reply With Quote
  #5 (permalink)  
Old
Computer Monkey
 
Join Date: May 2005
Posts: 1,191
Quote:
Originally Posted by angelmosue
I am trying to find certain pieces of text in a string field in a query.
Can you post the SQL of the query?
__________________
Me.Geek = True
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,206
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.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On