Extracting multiple substrings using VBA/Access 2003

  • Thread starter Thread starter jhawk
  • Start date Start date
J

jhawk

I'm creating an application that can create a multi-parameter query
on-the-fly using VBA and an Access form. As part of the query creation
process, the user is asked to identify a number of keywords that he would
like to use as search criteria. I've accomplished this using multi-select
list boxes and then concatenating the resulting selections into a single
string to be used in the final SQL statement.

However, due to the nature of the data being searched, there is the
possibility to have certain words repeated. What I want to do is: first,
extract from the final SQL WHERE statement all of the words that begin with
'* and end with *' and put them into an array or table; second, select only
distinct words from this quasi-recordset; third, identify which of these
keywords are actually found when the query is run; and finally, use these
remaining keywords as search criteria for querying a different dataset.

I have no idea how to do this. I'm a little familiar with the InStr, and
Left/Right/Mid functions, but don't if this is a good approach.

If anyone has any idea how to do this, or could suggest an alternate way,
please let me know; I would appreciate it.
 
I would check for duplicates either as the user enters words into the
textbox, or right before the initial data is pulled from these controls. It
seems like it would be easier to do here than find them after they're tucked
inside a contencated string.

For picking out works that start/end with *, I would use the standard vba
string functions (Instr, Mid etc) and add them to an array as you go.

As far as choosing additional words inside this list, you would loop the
array checking for that particular word as an element of the array.

Then you have the task of identifying which keywords were found when the
query is run. The only way I can think to do this is by running another
query on the returned data. Certain results should be telltale of specific
words being found, which can then be added to another array or contencated
string for use elsewhere. This doesn't sound like fun, really.

The Join() and Split() functions will load and unload an array from/to a
delimited string.

All of that said, this seems like an odd task to need to be performed. Is
there some way you may be able to restructure things a bit to avoid this need?

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Assuming that the list boxes are fed by queries, and you are
building your string in a variable, add the condition "Not In(strVariable) "
and requery every list after each selection is made from any list.

That would make it impossible to select dupes because if it is in the
variable
it is no longer in the lists.

Regards

Kevin
 
Back
Top