Wildcard Filtering on ADO Recordset
 

I am not an expert coder.. more of a copy cat

As an example I will just use 1 record tables.

I have a datatable with three fields. For ease I have just put in Excel (cell referance below)

D1 E1 F1
CSXX850 8505141 22971.92

and I have a mapping table with 2 fields

A1 B1
8??514* CP5140

I am using ADO to get a text file and read into a record set (datatable).

I am running into problems when filtering as it doesn't find the record using the wildcards above.

If I change mapping table to 850514* (Leaving last number off), it filters fine. I guess my question is if there another wildcard I need to be using instead of the question mark.

Code Below:

Global mrsData As ADODB.Recordset

Sub Wildcards2()

Dim i As Integer
Dim lsSymbol As String
Dim lsfilter As String

InitData

Call OpenRecordset(mrsData)

i = 1
lsSymbol = Trim(CStr(Sheets("Controls").Cells(i, 4).Value))

Do While Not lsSymbol = ""

mrsData.AddNew

mrsData!GLID = lsSymbol
mrsData!JDEAcct = Trim(CStr(Sheets("Controls").Cells(i, 5).Value))
mrsData!Amount = Trim(CStr(Sheets("Controls").Cells(i, 6).Value))

mrsData.Update

i = i + 1
lsSymbol = Trim(CStr(Sheets("Controls").Cells(i, 4).Value))

Loop

i = 1
lsSymbol = Trim(CStr(Sheets("Controls").Cells(i, 1).Value))

Do While Not lsSymbol = ""

lsfilter = "JDEAcct like '" & lsSymbol & "'"
mrsData.Filter = lsfilter

If mrsData.RecordCount 0 Then
mrsData!KLXAcct = Trim(CStr(Sheets("Controls").Cells(i, 2).Value))
End If

i = i + 1
lsSymbol = Trim(CStr(Sheets("Controls").Cells(i, 1).Value))

Loop

Call CloseRecordset(mrsData)

End Sub

Private Sub InitWildcards()
Set mrsWildcards = New ADODB.Recordset
mrsWildcards.CursorLocation = adUseClient
mrsWildcards.CursorType = adOpenDynamic
mrsWildcards.LockType = adLockOptimistic
With mrsWildcards
.Fields.Append "JDEAcct", adVarChar, 10
.Fields.Append "KLXAcct", adVarChar, 10
End With
End Sub

Private Sub InitData()
Set mrsData = New ADODB.Recordset
mrsData.CursorLocation = adUseClient
mrsData.CursorType = adOpenDynamic
mrsData.LockType = adLockOptimistic
With mrsData
.Fields.Append "GLID", adVarChar, 10
.Fields.Append "JDEAcct", adVarChar, 10
.Fields.Append "KLXAcct", adVarChar, 10
.Fields.Append "Amount", adDouble
End With
End Sub


Public Sub OpenRecordset(ByRef prs As ADODB.Recordset)
prs.Open
End Sub

Public Sub CloseRecordset(ByRef prs As ADODB.Recordset)
prs.Close
Set prs = Nothing
End Sub

Thanks.

Wildcard Filtering on ADO Recordset
 

So, in my case the mappings with question marks all started with 8 so I just had to build in some logic and do some combo filtering. Kind of a pain and probably not the most effecient but it worked.

Here is what I did.

Was:
lsfilter = "JDEAcct like '" & lsSymbol & "'"
mrsData.Filter = lsfilter

Changed to:

If Left(lsSymbol, 1) = "8" Then
If Right(lsSymbol, 1) = "*" Then
lsfilter = "JDEAcct like '*" & Mid(lsSymbol, 4, Len(lsSymbol) - 3) & "' AND JDEACCT like '8*'"
Else
lsfilter = "JDEAcct like '*" & Mid(lsSymbol, 4, Len(lsSymbol) - 3) & "*' AND JDEACCT like '8*'"
End If
Else
lsfilter = "JDEAcct like '" & lsSymbol & "'"
End If


mrsData.Filter = lsfilter