Tokenizing Strings

[kilando]

Hi Folks,

I'm primarally a Java/C/C++ programmer but I have to do a project for the company I'm working for in Visual Basic. (Building a Database)

I'm wondering if there is a way to tokenize strings entered by the user to aid in searching. For example, this database will contain various newspaper articles, I would like to have a search option to search by keywords so users could enter whatever they are looking for (A persons name and a place) and be able to search the list of keywords for each article.

ie: If the user enteres: Smith, Canada, Fishing

then I would like to be able to return all the records that have "Smith", "Canada" or "Fishing" listed as a keyword.

If this is not possible with VBA and Access (2000) please feel free to laught and ridicule me.

Thanks

 

[bryanbayfield]

I don't know much about Access VBA, but I'm sure it would be possible. There shouldn't be a lot of difference between doing it in Access VBA and externally using VB - you just have to make sure you set your progID in the object declaration ie Dim appACC as Access.Application.

I think you need the DoCmd object - not totally sure but I think that allows you to submit SQL queries which might be what you need.

In any case, I'm sure Tony Jollans will pop up in a sec to answer your query. Either that or there's plenty of Access forums ...

 

[Zathras]

...feel free to laught and ridicule...

We generally don't do that here in this Forum. That's just one of the things that make this an excellent site.

Without knowing your db structure, I can only speculate on two possible scenarios:

Scenario 1:
You have a table with 3 columns:

  TABKEYSEARCH
  ------------
  KeySearchKey  (Autonumber)
  KeyValue      (Text)
  DocNumber     (Number = FK to TABDOCUMENTS)

In that case, you could write SQL like:

  "SELECT * FROM TABKEYSEARCH S
    INNER JOIN TABDOCUMENTS D ON S.DocNumber=D.DocNumber
   WHERE KeyValue in (" + txtUserEntry.Text + ")"

Scenario 2:
You have just the TABDOCUMENTS table with a text column called KEYWORDS that contains key words contatenated with some special symbol (to avoid hitting on false combinations of characters). Perhaps like "^fishing^hunting^camping^"
In that case you can break down the input string into individual components and run multiple queries, storing the results in memory until all queries have been run:

  "SELECT * FROM TABDOCUMENTS
    WHERE KEYWORDS LIKE ""*^" + arrKeyWords(i) + "^*"""

Of course, you would need to write code to parse the user's input into the separate key words.

The first method would allow for using an index on the KeyValue column to speed up the searching. The second method would always require Access to do a full table scan. The first method takes a little more effort up front when putting documents into the database. It all depends on how many documents you plan on storing. If only a few thousand records, then either method could work, but if you have thousands of records then search speed becomes the most important factor.

 

[TonyJollans]

Hi kilando,

Having been summoned by Bryan here I am to answer your question, except that I'm not sure what it is.

Zathras has given a good answer about tables and searches to which I would only add a couple of minor points both relating to his second case - (1) depending on volumes and method of data input it might be worth coding to scan text and automatically write index keywords somewhere - and (2) the query could be done in one go with a UNION query.

However, as I said, I'm not sure what the real question is. Is it just that you don't know how to parse the user input string to get it into a usable format? Or is it that you don't know how to interact with Access from code (and is it VB or VBA)? Or is it that you don't know how to work within Access?

Please post back more explicitly if the answers you have are not sufficient.

Finally, I expose myself to the possibility of ridicule most days, but the people here are far too kind for that.

Enjoy,
Tony

 

[kilando]

Sorry for not being 100% clear on my question. I guess I shoudln't post here near the ned of a long day of work

Just to clarify, I'm using the Visual Basic that comes with Microsoft Access.

What I was hoping to find was a way to parse a string, like a tokenizer would in C/C++/Java. I have since found somethign like that (the split function) which does what I want but that still leaves me far from my goal.

My DB is like Zathras' Scenerio 2, I'll have the table for the records, where one column will contian a list of the keywords. I'll take the users search terms (from a text box), parse it into an array of keywords to serach for and then build the results.

The number of records stored is relitivly small (approx 300) but this could grow in the future so I was hoping to optimize the time it takes to do the query.

Now to the questions (or revised questions)

1) Is the split function the best function to be using in this situation?

and

2) What is the best way to get the size of a dynamic array? I'm looking ahead to the type of loop that I'll be using to do the search and I was leaning towards a for loop. The only problem with this is I don't know what the size of the array will be. With a Do loop, I'm stuck because I can't figure out how to tell if an element in an array is the last element.

Any help?

Thanks

 

[bryanbayfield]

I can't think of a better function than split for your purposes, at least if you want to "tokenize" your string into individual search terms.

I'm guessing that your search terms are space delimited - can individual search terms have spaces embedded in them or are they single words?

If they're single words and it's space delimted, then I'd use this function:

[tt]InStr$(text_box_string, variable_value_to_look_for)[/tt]

It returns >0 if the variable value for the record you're looking at is in the list of search terms the user specified.

Your second question: this is relatively simple - you can use UBound to find the upper bound slot number of your array. Be careful though as I think most if not all arrays in Access have a zero base unless you specify otherwise.

 

[kilando]

Thanks for the help everyone, I've got this just about working properly.