Access query via VBA (Word)

Your SQL isn't properly formed, specifically the WHERE clause. You should either double up the double quotes ( actually you use three double quotes, as shown below) or use Single quotes instead:

strSQLWhere = " WHERE tblXRefItems.Content LIKE ' " & strFilter & "*' "

or:

strSQLWhere = " WHERE tblXRefItems.Content LIKE """ & strFilter & "*' """

Thanks for your reply, Scott.

So, the SQL string ends up looking like this:

SELECT tblXRefItems.Sequence FROM tblXRefItems  WHERE tblXRefItems.Content LIKE ' 3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar u*'

Or this:

SELECT tblXRefItems.Sequence FROM tblXRefItems  WHERE tblXRefItems.Content LIKE "3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar u*' "

Unfortunately, nothing is returned either using code or pasting the string into the Access query.  There is definitely a record that meets the criteria. Have I missed something out or added something I should not have done?

Actually, you can re-write your where clause like:

strSQLWhere = "WHERE tblXRefItems.Content Like " & chr$(34) & strFilter & "*" & chr$(34)
debug.print strSQLWhere

Select allOpen in new window

The key is that the asterisk must be inside the quotes. When printed (see debug.pring line above)this might look like:

WHERE tblXRefItems.Content Like "3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar u*"

My second example was wrong. Should have been:

strSQLWhere = " WHERE tblXRefItems.Content LIKE """ & strFilter & "*"""

I'd suggest you copy that SQL over to your Access database into the SQL view of a new query and see if it works. May be something as simple as a misspelled word.

Thank you both. Unfortunately, this is still not working. Again, I have tried both the stored query, and putting the entire string into the code. In both cases, if I pass the identical text to that generated by the code - by using debug.print and copying said code - to the query, it returns a result. If I run it from the code, absolutely nada.

This is the code I use if using the stored procedure:

Private Function DBQuery_GetItemSequenceNumber(strFilter As String) As Long
Dim cQuery As New ADODB.Command
Dim pParam As ADODB.Parameter
Dim rResultH As ADODB.Recordset
    
    With cQuery
        
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "qXRefItemsMatch"
        
        If strFilter <> "" Then
            
            strFilter = Left$(strFilter, 70)
        
            Set pParam = .CreateParameter("EnterContent", adChar, adParamInput, Len(strFilter), strFilter)
            .Parameters.Append pParam
        End If
    End With
    
    Set rResultH = cQuery.Execute
        
    If Not rResultH.EOF Then
        DBQuery_GetItemSequenceNumber = rResultH("Sequence")
    End If
    
    Set cQuery = Nothing
    Set rResultH = Nothing
End Function

Select allOpen in new window

This is the text that gets passed:

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

Select allOpen in new window

This is the corresponding data in the Access table:

3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar ultricies, purus le

Select allOpen in new window

This is the LIKE statement in the Query:

Like [EnterContent] & "*"

Select allOpen in new window

This is the code if I use the SQL String option instead:

Private Function DBQuery_GetItemSequenceNumber(strFilter As String) As Long
Dim rResultH As New ADODB.Recordset
Dim strSql As String, strSQLSelect As String, strSQLFrom As String, strSQLWhere As String

    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)   'getcrossrreferenceitems returns max approximately 90-93

    strSQLSelect = "SELECT tblXRefItems.Sequence "
    strSQLFrom = "FROM tblXRefItems "
    strSQLWhere = "WHERE tblXRefItems.Content Like " & Chr$(34) & strFilter & "*" & Chr$(34)

    strSql = strSQLSelect & strSQLFrom & strSQLWhere

    With rResultH
        .CursorLocation = adUseClient
        .Open strSql, cn, adOpenDynamic, adLockOptimistic
    End With

    If Not rResultH.EOF Then
        DBQuery_GetItemSequenceNumber = rResultH("Sequence")
    End If

    rResultH.Close

    Set rResultH = Nothing

End Function

Select allOpen in new window

This is the SQL statement:

SELECT tblXRefItems.Sequence FROM tblXRefItems WHERE tblXRefItems.Content Like "3 Maecenas porttitor congue massa. Fusce posuere, magna sed pulvinar u*" 

Select allOpen in new window

I attach the relevant table and query too.

Any further ideas will be very much appreciated. Thanks.

You didn't attach anything.

Oops. Here it is.
Table.accdb