FILTER function in Access VBA? 1

[jjefferson]

Was recently upgraded to Access 2003, and wanted to try some new functions. I can get Split and Join to work just fine, but Filter is another story. The system doesn't seem to "recognize" it, in that I don't get the popup argument assistance that I do with Split and Join.

I can get to the help section and it's documented there, but I get a compile error when running.

My offending line reads:

aryNameList() = Filter(aryNameList, vbNull, False)

but VB comes back with "Compile error: Wrong number of arguments or invalid property assignment". I thought this would be a quick way to remove null elements from the array and saw this used in a VB6 situation.

Seems there is also a Filter property and Filter event; perhaps I'm not doing something to let VBA know that I want to utilize the function, and it's getting confused?

Thanks for your advice.

Jim

 

[TomHW]

I have noticed a few problems with the Filter function and the way you are attempting to use it. First, Filter wants to return a string array and will not allow you to return to a variant array, but it also wants a non-dim array. If you create an array of a certain size:

Dim astrArray(5) as String

and attempt to set it to the result of Filter(), even if there are fewer items being returned, it will give you an error. Your best bet is to Dim a string array with no size such as:

Dim astrArray() as String
astrArray = Filter(...

The next problem is that Filter() assumes you are passing it an array of strings. In this case you cannot have any Null values anyway because a string cannot hold the value Null. Even if you pass in an array of variants, the second term in the Filter() function will not let you check for Null (vbNull corresponds to a value that represents the type Null and not the value Null) and thus you will get an invalid use of Null error. Using a value in the second parameter such as "" will still return an invalid use of Null error because you will then be attempting to use a Null value in you array as a string.

I have also found that attempting to store the array returned by Filter() in the array you passed it, such as in your example, does not work.

Hope this helps,
Tom

 

[jjefferson]

Thanks, Tom.

My arrays are strings and not pre-sized (Array() as String), so we're okay there.

I did mistype my example; I was using the constant vbNullChar, but that likely doesn't make a difference in the issue you describe.

After I "Split" the text into the intial string array, I loop through it replacing zero length elements with vbNullChar. I changed that to replace empty elements with "." instead, as it will not appear in the text otherwise.

I also created another string array to receive the results of Filter. I think that addressed all the points you mentioned.

I still get the same error mentioned with these changes. Here's a (hopefully) better snapshot of what it looks like now:

Dim aryNameList() as String
Dim aryTemp() as String
Dim intNameCnt as Integer

aryNameList() = Split(txtNames, vbCrLf)
    
    intNameCnt = UBound(aryNameList)
    
    For i = 0 To intLoanCnt
    
        If Len(Trim(aryNameList(i))) = 0 Then aryNameList(i) = "."
        
    Next
    
    aryTemp() = Filter(aryNameList, ".", False)

The compile error occurs at this attempted use of the Filter function. I feel there must be something else stupid I have done (or have not done) that I can't see.

I can code around this so I don't have to use this function; it just seemed perfect for what I wanted to do. Thanks for any other thoughts or advice.

Jim

 

[PHV]

Replace this:
intLoanCnt
By this:
intNameCnt
And this:
= Filter(
By this:
= VBA.Filter(

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjefferson]

Thanks, PH! The "VBA." prefix to the Filter function did the trick. Sorry for the typo of the counter variable; I'm transcribing from a VNC session where I can't get the clipboard to work and I'm seeing "loan" and "name" all over the code.

I guess with a Filter property and Filter event in the mix, the compiler was a little confused? Anyway, by using VBA.Filter(), the argument popup magically appeared.

Just for grins and future reference, I thought I'd play and back out the changes TomHW suggested earlier to see what would happen. It seems that I can stick vbNullChar into empty array elements, and then use VBA.Filter to remove matches to vbNullChar. And it seems I can Filter an array back into itself (Array() = VBA.Filter(Array, vbNullChar, False)).

Thanks again; I can now move on to the next wall that needs a head-bangin'!

Jim