Searching strings for patterns.

[Darren Bartrup]

I have a userform called 'frmProcessReturns'.
On the form is a textbox named 'txtServicesReceived' and a listbox named 'lstServiceType'. The listbox has a single column and has the MultiSelect option set to fmMultiSelectMulti. Each value in the listbox is taken from an Access table called 'tblServiceType'.

The textbox is populated from a cell in a different workbook which is filled in by various little old ladies around the county, and being little old ladies they don't make it easy for the computer to figure out which services are being received - rather than put in something simple like 'MOW', they'll put in 'Meals-On-Wheels' (or sometimes 'Meals On Wheels' or maybe 'M.O.W').... where I just need 'MOW'. They could also add to this box the fact that they get 'Resi Care - Initial', where I just need to know that they get 'Residential Care'. Or in extreme cases they might put '2:30pm on Fridays' or 'I don't like crayons' (these are council workers after all)

Anyway, what I'm after is some kind of procedure (or just to be pointed in the right direction) on how to search the textbox (or the underlying cell) for each occurence of a word that matches these patterns.

Also, if it's not too much to ask, I'd like to be able to rank the words. For example if the answer is NFA (no further action) and RIP then I'd like it to rank NFA above RIP, but if the answer is MOW and RIP then MOW ranks higher than RIP.

The idea is that the listbox then highlights the best guess at services received.

Would this be easy to do in VBA? Or, seeing as there's already a connection open to a database could I use some kind of Access query for pattern matching?
I'm using Excel 2002 & Access 2000.

Please tell me if that makes sense! And any help would be greatly appreciated as usual.

 

[rjp]

There's never going to be a perfect solution to this unless you restrict the user entries to a predefined list in a list box or something.

If this is too inflexible then you may get some joy with regular expressions. They're a very powerful method of analysing patterns in strings and once you've got over the initial learning curve they're very easy to work with. You may even find them useful in other tasks.

You'll need to add a reference to the regular expression library (Tools > References in the VB Editor). On my machine the library is called "Microsoft VBScript Regular Expressions 5.5".

Also check out this page on MSDN that details their use:
MSDN Regular Expression API Reference

 

[Darren Bartrup]

Thanks for the reply rjp.

I am hoping to restrict the user entries eventually and turn it into an online collection form of some sort (but there's issues with security as the form includes personal information), but for now I have to work with what I've got and what the little old ladies are used to.

I'll go check out the regular expressions - never heard of them before