ADO recordset filtering error 2

[FarleySoftware]

I have an ADODB recordset that may contain multiple records.

If it contains multiple records, I want to filter the results further, by selecting records where the TranslationDescription field ends with the string value strShift.

Here's my code:

Private Function GetTranslation(strCode As String, _
        strShift As String) As Long
        

    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim strFilter as String
    
    ' Find matching record in translation table
    strSQL = "SELECT TranslationCode, " _
        & "TranslationDescription " _
        & "FROM tblTranslation " _
        & "WHERE TranslationInternalCode = '" & strCode & "' "

    rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    
    ' Return matching record; shows as 0 if not found
    Select Case rst.RecordCount
        Case Is = 1
            GetTranslation = CLng(rst!TranslationCode)
        Case Is > 1
            strFilter = "TranslationDescription LIKE '%" & strShift & "'"
            rst.Filter = strFilter ' <-- Error happens on this line
            If rst.Recordcount = 1 Then
                GetTranslation = CLng(rst!TranslationCode)
            Else
                GetTranslation = 0
            End If
        Case Else
            GetTranslation = 0
    End Select

    Set rst = Nothing
End Function

When the filter is applied, I get an error 3001 ("Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.")

I'm sure I'm overlooking something minor yet vexing.

Thanks in advance for taking a look.

 

[PHV]

You may try this:
strFilter = "TranslationDescription LIKE '%" & strShift & "[!]%[/!]'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FarleySoftware]

This does produce a result, thank you!

However, I need the TranslationDescription field to end with the strShift value.

Though it's unlikely given the current data, using the pair of % could return unwanted results.

This will do in the meantime & I thank you!

 

[JerryKlmns]

If you change your strSQL to

strSQL = "SELECT TranslationCode, " & _
                "TranslationDescription, " & _
                "Right(TranslationDescription , " & Len(strShift) & ") as myShift " & _
         "FROM tblTranslation " _
         "WHERE TranslationInternalCode = '" & strCode & "' "

and your filter to

strFilter = "myShift ='" & strShift & "'"

would you get an accurate result?

 

[FarleySoftware]

Actually ... yes That's an awesome idea! Just tested and it works great.

Appreciate all responses!

 

[JerryKlmns]

From time to time, I accidentaly get one of those!

Glad to be of help, and thanks for the star