Query showing unexpected results

[johnmerlino]

Does Access not support "CREATE FUNCTION"?



Also, I'm using | to separates alternatives. I hope that's supported.

[slave138]

In Access you can declare a function like:

[SCOPE (PUBLIC/PRIVATE)] Function [NAME]([PARAMETERS]) As [RETURN TYPE]

Example:

Public Function MyFunction(sText As String) As String

This creates a function called MyFunction which requires one parameter in string format. It returns a string value when a line is included:

MyFunction = StringValueToReturn

Scope, parameters and the return type are not required (though a Sub would probably be more appropriate if there is no return and defining scope can reduce headaches later).

[johnmerlino]

In my example, I passed three arguments to the function. I call the function twice but one time pass three and another time pass two. Some languages don't support overloading. Hence, you have to do something like:

function checkc(a,b,c){
c = c || "";
return c;
}

checkc('a','b','c');

Is this one of those cases?

[slave138]

You can use OPTIONAL parameters in the function declaration:

Public Function MyFunction(sText As String, OPTIONAL bTest As Boolean) As String

If you are going to include optional parameter they have to be grouped at the end (You can have 2 required then an optional but not one optional and 2 required or one required, one optional, one required)

[johnmerlino]

I put this in a VBA module:

Public Function NameMatch(ByVal pLast As String, ByVal pFirst As String, ByVal pMiddle As Variant, ByVal pSearchField As String) As Boolean

 
    Dim strReturn As String

    Dim objMatch As Match
    Dim colMatches As MatchCollection
    Dim RetStr As String

    strReturn = "/(\s|[pLast])(\s|,)[pFirst]/"

       If Len(pMiddle) > 0 Then
        strReturn = "/(\s|[pLast])(\s|,)[pFirst]\[spMiddle]/"
    End If

    Set objRegExp = New RegExp
    objRegExp.Pattern = strReturn
    objRegExp.IgnoreCase = True
    objRegExp.Global = True

    strReturn = objRegExp.Test(pSearchField)
    
    If (objRegExp.Test(pSearchField) = True) Then
    Set colMatches = objRegExp.Execute(pSearchField)
    
    Set oMatch = colMatches(0)

 
    RetStr = RetStr & oMatch.SubMatches(0)
    RetStr = RetStr & oMatch.SubMatches(1)
    NameString = RetStr
    
    End If

End Function

and then I put this in a query in access:

SELECT
  t.last_name,
  t.first_name,
  t.middle_initial,
  p.names_1,
  p.names_2
FROM temp_query AS t,
print_ready AS p
WHERE 
NameMatch(t.last_name, t.first_name, t.middle_initial, p.names_1) = True 
Or NameMatch(t.last_name, t.first_name, t.middle_initial, p.names_2) = True
And p.us_states_and_canada In ("FL", "NY");

and I get an error that says:

Undefined function NameMatch

So I go to VBA and select debug > compile and it says:

user-define type not defined

[johnmerlino]

I read in a tutorial that VBA supports MatchCollection. I tried changing to this:

Public Function NameMatch(ByVal pLast As String, ByVal pFirst As String,  ByVal pMiddle As Variant, ByVal pSearchField As String) As Boolean

 
    Dim strReturn As String

 
    Dim colMatches As MatchCollection
    Dim RetStr As String
    Dim objRegExp As RegExp

    strReturn = "/(\s|[pLast])(\s|,)[pFirst]/"

       If Len(pMiddle) > 0 Then
        strReturn = "/(\s|[pLast])(\s|,)[pFirst]\[spMiddle]/"
    End If

    Set objRegExp = New RegExp
    objRegExp.pattern = strReturn
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    
    If (objRegExp.Test(pSearchField) = True) Then
    Set colMatches = objRegExp.Execute(pSearchField)
    
    For Each match In colMatches
        record = match.Value
    Next

    NameString = record
    
    End If

End Function

And it still gives me user type undefined for " colMatches As MatchCollection"

I don';t know why I can't get this select statement to work right.

[ajetrumpet]

John,

Here we are 11 posts later and the thread is not marked as solved. So:

Attached is what I would do. You can see what the point of the solution is, as well as the patterns of the appends. Anything other ","/" " combinations that I missed can obviously be added to the list. Add your nested REPLACE() statement to the final and you'll be fine.

For future reference too, doing things this way is almost always MUCH easier than creating a complex query. IMO, use the small stuff to your advantage instead of stretching your brain to do a single complex task. It always saves time.

HTH. good luck.

[johnmerlino]

Thanks for response. I will test it out.

[johnmerlino]

Thanks again ajetrumpet. It was a helpful file you uploaded.

[johnmerlino]

@ajetrumpet

As I am looking over your suggestion, it seems like for each of those appends you do, it is not overwriting the existing append, but rather adding each append as another condition for possibles. I don't see any of these appends.

Update: Actually I do see them now. Nevermind.

[johnmerlino]

I also have a name like this that will never have a middle initial:
APEL,RICHARD & RHINA

Initially, I thought perhaps an append with this would work
SELECT Trim(Trim(temp_query.last_name) & "," & "* & " & Trim(temp_query.first_name) & "" AS Expr1
FROM temp_query;

But then I thought that the "*" character would actually end up in a column of possibles table, rather than a variable name, which could be any variable name. In this case, it was Richard, but in another case it would be something else.

So then I thought maybe this would work instead:

SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses INTO print_ready
FROM contacts, possibles
WHERE 
(
(INSTR(CONTACTS.NAMES_1, possibles.fullname) > 0)
Or
(INSTR(CONTACTS.NAMES_2, possibles.fullname) > 0) 
)
and
(
CONTACTS.us_states_and_canada = "FL"
or
CONTACTS.us_states_and_canada = "NY"
)
or
(
InStr([contacts.names_1],",")<>"0" And InStr([contacts.names_1],"&")<>"0"
or
InStr([contacts.names_2],",")<>"0" And InStr([contacts.names_2],"&")<>"0"
)
;

Do you have an idea of which one of these approaches would be best for this situation?

Thanks for response.

[ajetrumpet]

How many of those types of names do you have John? One?

If it's not too many, find a common ground to query them out on and insert a fake middle initial in those records that is identifiable to you. Then, copy one of my append queries and write the sql as appropriate.

make sense?

It's been a while since I've done this for you, and I apologize for not having time to take a look back at this.

[johnmerlino]

Thanks for response. There's 730,000 records, so there could be thousands of them with this kind of pattern:

APEL,RICHARD & RHINA 
APEL,RICHARD & RHINA B
APEL,RICHARD B & RHINA 
APEL,RICHARD B & RHINA B
APEL,RICHARD Bud & RHINA B
APEL,RICHARD B & RHINA Ann

I'm thinking the easiest way to accomodate this is as follows:

SELECT contacts.id, contacts.names_1, contacts.names_2, contacts.addresses INTO print_ready
FROM contacts, possibles
WHERE 
(
(INSTR(CONTACTS.NAMES_1, possibles.fullname) > 0)
Or
(INSTR(CONTACTS.NAMES_2, possibles.fullname) > 0) 
)
or
(
InStr([contacts.names_1],",")<>"0" And InStr([contacts.names_1],"&")<>"0"
or
InStr([contacts.names_2],",")<>"0" And InStr([contacts.names_2],"&")<>"0"
)
;

However, right now this is giving me a "Cannot open database. It may not be a database your application recognizes, or the file may be corrupt." That popup shows up after a couple of minutes running the query.

[ajetrumpet]

the db is probably getting overloaded john.

after a couple of minutes doing that WITH A CROSS JOIN!? You're kidding, right?

It's probably out of memory or worse. Did you try appending it to the single table I did in the example? Do you recognize that what I did does NOT use a cross join at all? They are stacked queries, which takes the burden of complexity off of the db engine. If you do it that way, you won't get that error.

[johnmerlino]

Ok, I initially wanted to do your solution. The thing is the name could be anything, so would this work?

SELECT Trim(Trim(temp_query.last_name) & "," & "* & " & Trim(temp_query.first_name) & "" AS Expr1
FROM temp_query;

I use "*" because that's the name that comes before "&" and it can be anything in a list of 730,000 records.

Thanks for reply.