Results 1 to 6 of 6
  1. #1
    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

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Have a look at using the InStr Function in a WHERE clause.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    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 on the Tiger 800 or the Norton

  4. #4
    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.

  5. #5
    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    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 on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •