Results 1 to 10 of 10
  1. #1
    Puebles is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Lansing, MI
    Posts
    14

    How do you search a single text field for 32 specific words or phrases?

    Hello,

    I am new to Access....migrating from FoxPro. I am trying to replicate a function I use to parse a comment field to determine that the comment meets certain requirements. The existing code loops through the table and examines each comment for a series of words or phrases. Currently I am searching the comment field with 32 distinct words or phrases. The segment of code I am trying to replicate looks like this:

    .............
    If At("MAILED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("FAXED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif


    If At("RETURNED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("VOIDED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("ISSUED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    ............

    In VFP the command AT(TextString, TextSearched) returns the character position of the occurrence of the TextString, zero if not found.

    Upon completion of the existing VFP code, records with 0 are deleted, the remaining comments are compared by date to determine response time.

    I can replicate this with multiple queries, but assume there is a programmatic method. Please help!

    Thank you in advance.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Where are these literal text phrases coming from? is this something you are importing into your DB?

    I would opt for assigning a key to represent a phrase LIKE "Faxed" and then locate that. If this is not an option, I would look at some of the many "Search Form" examples and incorporate that code. I don't know how your data is structured but you did mention "Parse"... there are many examples that incorporate wildcards to search for specific text criteria within a phrase. You could then use a CASE select with your result perhaps.

    Just some ideas....

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Describe what this line:
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    ...does in VFP, please.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There must be more to this you aren't showing.

    AT(TextString, TextSearched) is a custom user defined function?

    How does the procedure 'loop through table'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Puebles is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Lansing, MI
    Posts
    14
    The total code includes table and environment preperation. The 32 AT() commands: one for each word/phrase searched, are nested in a Do While loop. The AT() command is a stock VFP command. More on the command can be found here.

    Go Top
    Do While !Eof()
    If At("MAILED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("FAXED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("RETURNED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("VOIDED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    If At("ISSUED", Upper(comments.alc_commen)) >0
    Replace comments.EXCLUDE With comments.EXCLUDE+1
    Endif
    Skip
    Enddo

    I add the field EXCLUDE to the table and set the value to zero prior to running the code. By adding 1 to that field for each find the result can range from 0 (no keywords found) to 32 (indicating that all 32 keywords or phrases are found in that comment line). Originally when designed I coinsidered the number might indicate the strength of the comment's validity. However, in the end, the result is only relevant as zero or not zero.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    So the quantity "32" has no relevance other than it could possibly be described as a glass ceiling?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How does this code 'loop through table'?

    In VBA you need to either open a recordset or have code move through records of a form.

    I don't understand the Do While. Why would you run the loop if the recordset is at EOF? Don't know what Go Top and Skip are for. Are "MAILED", etc fields of recordset or literal text string? Is comments.alc_commen a textbox on form? Is it unbound? Is it in the form Header section?



    Maybe:

    With Me.RecordsetClone
    If Not .EOF Then
    If InStr(Me.alc_commen, !MAILED) > 0 Then
    'do somthing here, I also don't understand the Replace but there is a function Replace()
    End If
    ...
    .MoveNext
    End If
    End With

    InStr() is not case sensitive. I think the arguments are reverse of AT().
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure I understand the issue, but I'd put all the words and phrases into a separate table. Each would be a record. I would then set up a loop with this table and the table with your comments field; and the appropriate counters. Check each comment against each word/phrase.

    Good luck.

  9. #9
    Puebles is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Lansing, MI
    Posts
    14
    Yes, 32 is a glass ceiling.

    Skip moves to the next record.

    The operator ! means not, so !eof() means not end of file

    Sounds like InStr() is the function I need. Glad to here it is not case sensitive. I have been searching through teh command index I have. Didn't think of one starting with an "I". Unfortunately, I have no crosswalk.

    I was going to set these words/phrases into a table once I get it working. I hope to make this entire function one click for others so that I do not have to run it.

    Thank you all for the help.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Puebles View Post
    I was going to set these words/phrases into a table once I get it working. I hope to make this entire function one click for others so that I do not have to run it.
    June7 has offered a great translation of your code. I am not sure much more advice can be offered without an explanation of what your task is. I understand the desire to automate and loop through some data.

    How are you getting this data? The answer may help determine best how to put it into a table.
    What does the raw data look like and how is it formatted? Is it in a large paragraph of text?

    It seems you have only 5 possible key words you are searching for. Is a possible solution to assign an FK to the data you are looping through, an FK that represents any one of the five key words?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-02-2013, 04:33 PM
  2. Mulitple word search in single field
    By Lowell in forum Queries
    Replies: 23
    Last Post: 06-28-2013, 09:32 PM
  3. Creating a search button for key words
    By In need in forum Access
    Replies: 10
    Last Post: 05-01-2012, 04:21 AM
  4. Replies: 1
    Last Post: 03-19-2012, 11:03 AM
  5. Replies: 2
    Last Post: 08-31-2010, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums