Word VBA passing parameter to access query with wildcard

MonteDelMar
MonteDelMar used Ask the Experts™
on
I have the following code in VBA to return information from an Access Table.

Private Function DBQuery_GetItemSequenceNumber(Optional strParameter As String, Optional strFilter As String) As Long
Dim cQuery As New ADODB.Command
Dim pParam As ADODB.Parameter
Dim rResultG As ADODB.Recordset
    
    With cQuery
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "qItemsMatch"
        
        If strParameter <> "" And strFilter <> "" Then
            If Mid(strFilter, Len(strFilter)) = vbLf Then strFilter = Left$(strFilter, Len(strFilter) - 1)
            If Mid(strFilter, Len(strFilter)) = vbCr Then strFilter = Left$(strFilter, Len(strFilter) - 1)
            strFilter = Trim$(strFilter)
            strFilter = Left$(strFilter, 70)
            Set pParam = .CreateParameter("ParaDetail", adChar, adParamInput, Len(strFilter), strFilter)
            .Parameters.Append pParam
        End If

    End With
    
    Set rResultG = cQuery.Execute
    
    If Not rResultG.EOF Then
        DBQuery_GetItemSequenceNumber = CLng(rResultG("Sequence"))
    End If
        
    Set cQuery = Nothing
    
End Function

Open in new window


This is the design of the Access Query
If I run the query manually, then it works. However, when I pass the content via VBA, there are not results returned. I guess I am passing something the wrong way in the code but can anyone tell me what it should be, please?

Many thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Since the wildcard is already part of the criteria all you should need to do is pass an empty string for the parameter when you want to return all records.

That empty string would replace '[Enter Content]' in the criteria leaving the wildcard '*'.

Author

Commented:
Thanks for your response.

This is the sort of data I am working with.

This is the type of data in the table.
I would pass the first 70 characters of a potential row and, in this case, expect the query to return row 3. The string I am passing is:

  3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar u

If I run that manually, I get the expected result but, if I pass the string in the code, as the value for strFilter, it does not return any records.

I have compared the text in case there are any hidden characters but there do not seem to be, and the manual and code strings are the same length.

Author

Commented:
Fabrice Lambert - I have received an email saying you commented on this question but I cannot see your reply. Would you be kind enough to post it again, please?
I seem to have resolved this by using a SQL statement rather than a stored procedure.

    strSQLSelect = "SELECT *"
    strSQLFrom = " FROM [tblXRefItems]"
    strSQLWhere = " WHERE [Content] ='" & strFilter & "'"
    strSql = strSQLSelect & strSQLFrom & strSQLWhere

Thank you very much for your input, Norie. Any responses are always much appreciated.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial