Results 1 to 10 of 10

Thread: text filtering

  1. #1
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Question Unanswered: text filtering

    Hello,
    I created a database using an HTML parser. Due to the fact that parsers don't work on text recognition but on HTML architectural analysis, I had to extract full parts of HTML pages. My problem is that in this full part on ly a small and precise part is interesting for my databse work. Indeed this part easily indentifiable because there's a specific word at the start of this chain of text.
    For example, the field to be filtered contains this text value:

    "Organisation details field"
    "Name: Pacific Northwest National LaboratoryAddress: 902 Battelle Blvd PO Box 999Richland, WA 99352UNITED STATESType: Consultancy; Research; Non CommercialNumber of Employees: > 500Details: The Pacific Northwest National Laboratory (http://www.pnl.gov/main/welcome/index.html) is operated by Battelle Memorial Institute for the United States Department of Energy. At Pacific Northwest, we deliver breakthrough science and technology to meet key national needs. We also apply our capabilities to meet selected environmental, energy, health and national security objectives, strengthen the economy, and support the education of future scientists and engineers.Turnover: 478 million euroKeywords: ; Phontonics; Optics; Chalcogenide; Microstructurual Characterization; Properties"

    And I want this field (which will be named keywords after the filtering operation) to contain only this text value :" ; Phontonics; Optics; Chalcogenide; Microstructurual Characterization; Properties".

    I assume that I have to use the wisard and configure it to search for the text value "keywords" through the "Organisation details field", but being a newbee with access, I'd like to know what is the command line or the trick to do that ?
    thanks for your help!

    nico

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    correction 6 June 17:09 - the line should read:
    TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords: ;") + 11)



    don't know about wizard-magic, but the following function should return the keywords from your example:

    private function TheKeyWords (longText as string) as string
    TheKeyWords = mid$(longtext, instr(1, "Keywords: ;", longtext))
    exit function

    if longText is your example, function returns: "Phontonics; Optics; Chalcogenide; Microstructurual Characterization; Properties"

    izy
    Last edited by izyrider; 06-04-03 at 12:15.

  3. #3
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88
    In which mode shoul I use this code line? As a query a macro? I am really a newbee with Access!
    thank you

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    correction 6 June 17:09 - two lines should read:
    TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords: ;") + 11)



    aha! try the following click-by-click to illustrate the process.

    assumptions: you have an access file called "thedb.mdb" which contains a table called "myTable" which in turn contains a field "OrgGen" containing the long extracted text as in the example in your first post.

    start thedb.mdb

    hit <F11> ...the database window becomes visible (if it wasn't already).
    left pane of the database window: click <modules>
    top of the database window: click <new> ...the code window opens

    type two lines in the code window ending with <Return> on each:
    public function TheKeyWords (longText as string) as string
    TheKeyWords = mid$(longtext, instr(1, "Keywords: ;", longtext))

    access should have added a third line "exit function" and changed the capitalisation.


    so now your module looks like:
    Public Function TheKeyWords (longText as String) as String
    TheKeyWords = Mid$(longtext, Instr(1, "Keywords: ;", longText))
    Exit Function

    <Ctrl><S> - save - give it a name like "myModule"
    close the code window

    left pane of the database window: click <queries>
    top of the database window: click <new>
    in the new query dialog box that appears, select <design view>
    in the show table dialog box that appears, select tables ¦ myTable
    close the show table dialog box

    you now have the query design window displayed.
    top of this window lists the fields in myTable
    double-click OrgGen... the field name is copied to the query "grid"
    <Ctrl><S> - save - give it a name like "myQuery"

    in the first empty column of the query design grid, click once in the top ("field") box .
    in the box, type:
    thekeywords([OrgGen])
    click somewhere else in the query design grid
    <Ctrl><S> - save
    Close the query design window

    in the database window, double-click myQuery. did it fly?

    once you get this query working, you should read about update queries or make-table queries. you could make a form with a command button that runs the query... etc.
    ...you have some fun experiments ahead of you.

    if you can't get started - zip & post your mdb. explain what you are trying to achieve and someone will help you out.


    izy

    NOTE: instr(1, "Keywords: ;" etc is looking for EXACTLY
    uppercaseK-lowercaseeywords-colon-space-semicolon because that is what you have in your example.
    If you get unexpected results for some entries it may be that the capitalisation or puctuation differs from entry to entry. There are solutions, but you would need to state the problem.

    ALSO NOTE: i have not included any error checking! expect the unexpected if your long text string is empty or doesn't contain "Keywords: ;" or there are no keywords.
    Last edited by izyrider; 06-04-03 at 12:16.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    correction 6 June 17:09 - the line should read:
    TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords: ;") + 11)



    ooops: the function as described actually returns
    "Keywords: ; Phontonics; Opti etc. "

    to perform as advertised, the function in the module needs to be changed to:

    Public Function TheKeyWords (longText as String) as String
    TheKeyWords = Mid$(longtext, Instr(1, "Keywords: ;", longText)+11)
    Exit Function

    so that it doesn't return the "Keyw... etc"



    izy
    Last edited by izyrider; 06-04-03 at 12:17.

  6. #6
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88

    Thumbs up

    Thx Izy!!!!
    I'll try that at once! Your answer is remarkable, thank you again!
    I'll be back if any error message pops up!
    c ya

  7. #7
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88
    Izy,
    I tried the function except that I get an error message it works. But apperently the Instr function doesn't work correctly because on the resulting field of the function I get the same field but without the first eleven characters!!!
    This appears in the module code line:
    "Public Function TheKeyWords(longText As String) As String
    TheKeyWords = Mid$(longText, InStr(1, "Keywords: ;", longText) + 11)

    End Function"

    What's wrong??? I correctly did step by step what you told me..... Is it because there is "End function" and not "Exit function"? Dunno...

    Originally posted by izyrider
    (...) double-click myQuery. did it fly?
    What do you mean by fly?


    thanks for your answers
    nico
    Last edited by nicolascaprais; 06-04-03 at 07:49.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740


    it SHOULD be End Function (sorry)

    second error is just as stupid. it's the wrong way round!

    Edit ( <Alt><F11>) the function so it reads:
    TheKeyWords = Mid$(longText, InStr(1, longText, "Keywords: ;") + 11)

    (sorry again)

    izy

  9. #9
    Join Date
    Apr 2003
    Location
    paris, FRANCE
    Posts
    88
    Izy you are the king!!!!
    Because I have already studied a certain number of computer language I knew it was a dumb error like that.... I was printing out all the help forms on InStr and Mid functions to see which argument had a wrong input... But after your correction added it works fine now!!!
    thanks a lot again!
    If you are ever in Paris someday PM me or send me an email or contact us at www.hlpdeveloppement.fr, we'll invite you to eat out man!
    thanks again
    c ya

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    enfin!

Posting Permissions

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