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!
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.
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))
<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:
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.
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.
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)
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?
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!