By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,283 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,283 IT Pros & Developers. It's quick & easy.

Problem with Function used in Query

P: 72
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal lngArticleID As long) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID=" & lngArticleID & " AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. End Function
Code:(sql)
SELECT tblLiteratureArticles.*
FROM tblLiteratureArticles
WHERE KeyWordsInStr(Forms!Form1!txtKeywords,tblLiteratur eArticles!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:

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal strArticleID As String) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID=" & strArticleID & " AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. End Function
Yet, the query still does not work. Can anyone spot the problem?

Thanks!
Aug 3 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,669
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:

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal lngArticleID As long) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID=" & lngArticleID & " AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. End Function
Code:(sql)
SELECT tblLiteratureArticles.*
FROM tblLiteratureArticles
WHERE KeyWordsInStr(Forms!Form1!txtKeywords,tblLiteratur eArticles!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:

Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal strArticleID As String) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID=" & strArticleID & " AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal strArticleID As String) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID='" & strArticleID & "' AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. End Function
Aug 3 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
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/show...hreadid=575414

Linq ;0)>
Aug 3 '07 #3

P: 72
Try the change in Line #17 where strArticleID is Delimited by Single Quotes:
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal strArticleID As String) As Boolean
  3.     Dim intPos As Integer
  4.     Dim strKeyWord As String
  5.  
  6.     KeyWordsInStr = False
  7.  
  8.     Do
  9.         intPos = InStr(1, strKeyWords, ",")
  10.         If intPos = 0 Then
  11.             strKeyWord = Trim(strKeyWords)
  12.         Else
  13.             strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  14.             strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  15.         End If
  16.         If IsNull(DLookup("ArticleID", "tblArticleKeyword", _
  17.             "ArticleID='" & strArticleID & "' AND Keyword Like '*" & _
  18.             strKeyWord & "*'")) Then
  19.             Exit Function
  20.         End If
  21.     Loop Until intPos = 0
  22.  
  23.     KeyWordsInStr = True
  24.  
  25. End Function
Thank you! It worked perfectly!
Aug 3 '07 #4

P: 72
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/show...hreadid=575414

Linq ;0)>
Thanks! The link was helpful!
Aug 3 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
There's a lot of good stuff up in the attic!

;0)>
Aug 3 '07 #6

ADezii
Expert 5K+
P: 8,669
Thank you! It worked perfectly!
You are quite welcome.
Aug 3 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.