Problem with Function used in Query

[mforema]

Hi Everybody,

I have a function that worked perfectly when it was used in a query. I received help in creating this function from this discussion: Help with Query and Subform - post #18

However, I had to change the Data Type of one of my fields ("ArticleID" ) from a number to text. Now, the query doesn't work, and I know it's because of the change that I made. The original code and sql are as follows:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal lngArticleID As long) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID=" & lngArticleID & " AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Code:(sql)
SELECT tblLiteratureAr ticles.*
FROM tblLiteratureAr ticles
WHERE KeyWordsInStr(F orms!Form1!txtK eywords,tblLite ratureArticles! ArticleID)

I knew that I would need to change the data type for the variable lngArticleID from 'long' to 'string.' So, I did, as shown below:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal strArticleID As String) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID=" & strArticleID & " AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Yet, the query still does not work. Can anyone spot the problem?

Thanks!

[ADezii]

Hi Everybody,

I have a function that worked perfectly when it was used in a query. I received help in creating this function from this discussion: Help with Query and Subform - post #18

However, I had to change the Data Type of one of my fields ("ArticleID" ) from a number to text. Now, the query doesn't work, and I know it's because of the change that I made. The original code and sql are as follows:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal lngArticleID As long) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID=" & lngArticleID & " AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Code:(sql)
SELECT tblLiteratureAr ticles.*
FROM tblLiteratureAr ticles
WHERE KeyWordsInStr(F orms!Form1!txtK eywords,tblLite ratureArticles! ArticleID)

I knew that I would need to change the data type for the variable lngArticleID from 'long' to 'string.' So, I did, as shown below:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal strArticleID As String) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID=" & strArticleID & " AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Yet, the query still does not work. Can anyone spot the problem?

Thanks!

Try the change in Line #17 where strArticleID is Delimited by Single Quotes:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal strArticleID As String) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID='" & strArticleID & "' AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

[missinglinq]

You might also want to take a look at this tutorial by our Forum Leader, NeoPa. He explains the mysteries of the single and double quotes, when and how to use each!

http://www.thescripts.com/forum/showthread.php?threadid=575414

Linq ;0)>

[mforema]

Try the change in Line #17 where strArticleID is Delimited by Single Quotes:

Public Function KeyWordsInStr(ByVal strKeyWords As String, _
                              ByVal strArticleID As String) As Boolean
    Dim intPos As Integer
    Dim strKeyWord As String
     
    KeyWordsInStr = False
     
    Do
        intPos = InStr(1, strKeyWords, ",")
        If intPos = 0 Then
            strKeyWord = Trim(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
            "ArticleID='" & strArticleID & "' AND Keyword Like '*" & _
            strKeyWord & "*'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Thank you! It worked perfectly!

[mforema]

You might also want to take a look at this tutorial by our Forum Leader, NeoPa. He explains the mysteries of the single and double quotes, when and how to use each!

http://www.thescripts.com/forum/showthread.php?threadid=575414

Linq ;0)>

Thanks! The link was helpful!

[missinglinq]

There's a lot of good stuff up in the attic!

;0)>

[ADezii]

Thank you! It worked perfectly!

You are quite welcome.