Pattern Matching or wildcards

[andykent]

Does anyone know of the best way to do the following:

We have a field containing surnames and we want to only flag the names which contain illegal characters (eg £, $, ^, & , * etc) and ignore any names which have [A-Za-z] and spaces, hypens and apostrophes

I can see we can use like and [A-Z] and we can do searches such as like "*&*" to find any string containing a &, but for the number of "illegal" characters we want to find that's a lot of OR this OR that etc!

We can't use custom function as we are using C++ front end and must rely on query with native Access functions sush as Instr/Like !

Any ideas?



Andy

 

[MichaelRed]

Of course, if you could use a 'Module', the exercise is relatively simple. the core issue seems -to me- to be thte need to pares out each character, and I can not see any way to do this with out a module/function. There are a few 'papers'? which suggrest that VB can use C (or C++) procedures, simply by adding code to add/strip the null char to the end of the (C / C++) strings.

Alternatively, any FUNCTION procedure used with a POMDB (Plain Old MDB) file must be referecable from within that MDB, I do not necessarily understand why a VBA function placed in the MDB file (Public Module) will not work for you.

Assuming that you CAN use the procedure, the following is a START on the process. It does cover the specific cases you noted, however it is probable that you will modify the list of 'special' characters to include some additional punctuation marks (e.g. the dot/period(.)?.

Public Function basIsAlpha(strIn As String) As Boolean

    Dim MyChr As String
    Dim Idx As Integer

    basIsAlpha = True

    For Idx = 1 To Len(strIn)
        MyChr = Mid(strIn, Idx, 1)
        Select Case MyChr
            Case "a" To "z"
                'Legal Chr, Do Nothing

            Case "A" To "Z"
                'Legal Chr, Do Nothing

            Case " ", "-", "'"          'Add other "Special" Characters here
                'Legal Chr, Do Nothing

            Case Else
                basIsAlpha = False
        End Select
    Next Idx

End Function

In the MDB, open a general / publlic module and just past the above into it. To refer to it in the query (if you are using the query builder):

GoodName: basIsAlpha([fieldname])

or in the query SQL:

basIsAlpha([fieldname])]) AS GoodName MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[andykent]

Thanks for this Michael - though we looked into this a long time ago and using C++ you can't use ANY custom functions in the back end database

You'd think this would be easy to do wouldn't you!

We were frustrated because things that were easy in code suddenly we had to find ways of doing it using queries - believe me a lot of nested IIF statements were used to replicate some simple functions!

Unless you know otherwise!!??



Andy

 

[MichaelRed]

Not being a user of C/C++, I cannot speculate on the REASON for the problem, but I can assure you that custom procedures which are in the database are available to queries. One way this MAY be able to be done is to add the module containing the referenced procedures to your project through the references . I know that I can add VB(6) modules (and class modules) to the Ms. Access (ver 2K +) via the references function. I believe that Ms. Access (ver 2K +) can also add references to VB(6) modules from within Ms. Access. I do not rember the details, but I think I was sucessful in adding such a reference - but it was just a trivial exercise to see if it would 'work', not anything I had a real "NEED" for.

Otherwise, the function process should work very much the same in C++ as in VB(A | 6), just adjusting for the string termination (you need to add a Chr(0) to the string provided by Ms. Access to process in C++ ?) And implement the procedure in C++ (which should be trivial?). Probably not as easy as I imagine, but certainly SEEMS to be a workable approach. I can't imagine that the legions of C++ programs which use Ms. Access (.MDB) databases are doing w/o custom functions.

Try asking how to use/implement custom functions in the VC++ forum(s)?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over