Filter Function

[wperzel]

Can anyone give a brief example of the use of the Filter Function.
i.e. Filter(sourcearray,match[,include[,compare]]).

I am trying to use it in a module to identify those records where a match for a piece of text appears in a field so I can write a table of cross references.

i.e. Create a record (including field A1 and field X1) for each time field A2 from Table A contains a reference( as part of the field) to field X1 in control Table X.

So far, no matter what, my Match_IR variable in the following code, always returns an "empty" even if I know there should be a match in the records selected.

Match_IR = Filter(Find_IR, Cons_Pt, True, vbTextCompare)

I have tried various configurations of "Like *" & fieldX1 & "*" as well, to no avail.

If you have a small example to demonstrate the concept it would be appreciated. If I can trap any kind of reaction from the function I can move on with balance of the code.

Warren

 

[Alexandre]

I wrote this as an example, maybe i will help you to find what is wrong with you code:

Function Trial()
Dim Arr(12) As String
Dim VarArr As Variant
Dim i As Integer


Arr(1) = "lhk"
Arr(2) = "lhgjewk"
Arr(3) = "lhhrhk"
Arr(4) = "lhjtk"
Arr(5) = "lhtqek"
Arr(6) = "lhkjfg"
Arr(7) = "lhbxk"
Arr(8) = "lhiyk"
Arr(9) = "lh5sjdrk"
Arr(10) = "lhpuzwsk"
Arr(11) = "lhr5k"


VarArr = Filter(Arr(), "j", True, vbTextCompare)


For i = 1 To UBound(VarArr)
  Debug.Print VarArr(i)
Next


Erase Arr()
Set VarArr = Nothing


End Function

Alex

 

[wperzel]

Alexandre,
Thanks for your help.
I may be using the function other than in its intended purpose. I am giving the function an array of 1 and asking if a string occurs in the given array.

Based on what you have given me, it seems if the size of VarArr is greater than 0(zero), my answer is that the string does exist in the sample and my code can react accordingly.

However, my original problem was that the function returned a value of "empty" even if I knew the string existed in the test record.

Can you confirm that the function should return a value of empty if the string does not exist and 1 or greater if it does exist?

Warren

 

[Alexandre]

There seems to be some misunderstanding here.
What is the use of a string array of one element? Why not using a string?
Filter does not return any number, but an array, that can be empty (no element) if there is no match in the original array.

Show us your code

Alex

 

[wperzel]

Okay, here goes. Remember, to this point I am only trying to trap those records that satisfy a condition. (i.e. inclusion of a field from records in another table. Ergo the cross reference issue.Option Compare Database
Function Cross_Ref()

' Off
DoCmd.SetWarnings False

Dim recIR_No, recPt_No, recIR_Pt, Cons_Pt As String
Dim Find_IR As String
Dim Match_IR As Variant

Dim dbs_IR As ADODB.Connection
Set dbs_IR = CurrentProject.Connection

Dim TblDef_Cons, TblDef_IR As String
TblDef_Cons = "IR_No_Cons"
TblDef_IR = "IR_No_Tbl"

Dim rst_Cons As New ADODB.Recordset
Dim rst_IR As New ADODB.Recordset
rst_Cons.Open TblDef_Cons, dbs_IR, adOpenKeyset, adLockOptimistic
rst_IR.Open TblDef_IR, dbs_IR, adOpenKeyset, adLockOptimistic

rst_Cons.MoveFirst
While rst_Cons.EOF = False
Cons_Pt = "Like " & "*" & rst_Cons!IR_Cons & "*"

rst_IR.MoveFirst
While rst_Cons.EOF = False

‘Alexandre, the following chunk of code just runs through records until I get to the first record that I know has a cross reference imbedded in it.

Find_IR = rst_IR!Response
If rst_IR!IR_No <> "ACC.UNCA-1-5" Then
rst_IR.MoveNext
Else

Match_IR = Filter(Find_IR, Cons_Pt, True, vbTextCompare)

If IsNull(Match_IR) Or Match_IR = "" Then
MsgBox Match_IR

Else: MsgBox "Not Null or empty"

End If
End If

' rst_IR.MoveNext

Wend

Wend

End Function
Function Filter(strFind() As Variant, strMatch As String, Include, vbTextCompare)
MsgBox strMatch
End Function

 

[Alexandre]

You are not using Filter in its correct context. Your Find_Ir is a single string value, not an array.

To check for the existence of a substring in a string, use InStr:
If InStr(StartAtThisPosition, SearchInThisString, SearchForThisSubString, CompareThisWay)

Ex:
If Instr(1,Find_IR,Cons_Pt, vbtext)>0 then 'There is a match

Now, when I see your Con_Pt
Cons_Pt = "Like " & "*" & rst_Cons!IR_Cons & "*"

I suspect that you are trying to determine if Find_IR is like a certain value, which is again a different thing...

Alex

 

[wperzel]

Alexandre,

Thanks.

I was trying to carry water in an envelope but I didn't know pails had been invented.

Instr worked just fine.

Warren