Help with Query and Subform

**kepston** · Jul 31 '07, 11:33 AM

Originally posted by mforema

http://www.thescripts.com/forum/thre...arch+form.html - post #7

OK.
The code worked on my database because I am searching the equivalent of the text of the Article rather than a table of keywords. My text is only a few lines long for each record. Your situation is obviously much larger.
Anyway, I have worked out a method that should help you.
It requires a second Public Function to count the number of keywords to search for and combines that with the number of keywords found (using the ANY search, not the ALL search). So, if there are 2 keywords to find, but the article only contains 1 of them, that article is discarded.

Originally posted by FishVal (previously #8)

[CODE=vb]Public Function KeyWordsInStr(B yVal strKeyWords As String, _
ByVal varField As Variant) As Boolean

Dim intPos As Integer
Dim strKeyWord As String

KeyWordsInStr = False

Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
strKeyWord = Trim(strKeyWord s)
Else
strKeyWord = Trim(Left(strKe yWords, intPos - 1))
strKeyWords = Trim(Mid(strKey Words, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
Loop Until intPos = 0

End Function[/CODE]

[CODE=vb]Public Function CountKeywords(B yVal strKeyWords As String) As Integer
Dim intPos As Integer

CountKeywords = 0
If IsNull(strKeyWo rds) Then Exit Function

Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
CountKeywords = CountKeywords + 1
Else
strKeyWords = Trim(Mid(strKey Words, intPos + 1))
CountKeywords = CountKeywords + 1
End If
Loop Until intPos = 0

End Function[/CODE]
Your SQL then becomes something like[CODE=sql]SELECT [your_fields]
FROM
(tblLitCategori es INNER JOIN tblLiteratureAr ticles ON
tblLitCategorie s.Abbreviation = tblLiteratureAr ticles.Abbrevia tion)
INNER JOIN tblArticleKeywo rd ON
tblLiteratureAr ticles.ArticleI D = tblArticleKeywo rd.[Article ID]
WHERE
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeywo rd.Keyword)=Tru e
AND tblLitCategorie s.Selected=True
GROUP BY [your_fields]
HAVING (((Count(tblArt icleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));[/CODE]
Substitute [your_fields] with a list of the fields you want to see, make sure you duplicate that list in the GROUP BY clause, otherwise you will see error messages about fields not being included as part of an aggregate function.

Hopefully you should be really close now.

**mforema** · Jul 31 '07, 02:05 PM

Originally posted by kepston

OK.
The code worked on my database because I am searching the equivalent of the text of the Article rather than a table of keywords. My text is only a few lines long for each record. Your situation is obviously much larger.
Anyway, I have worked out a method that should help you.
It requires a second Public Function to count the number of keywords to search for and combines that with the number of keywords found (using the ANY search, not the ALL search). So, if there are 2 keywords to find, but the article only contains 1 of them, that article is discarded.

[CODE=vb]Public Function CountKeywords(B yVal strKeyWords As String) As Integer
Dim intPos As Integer

CountKeywords = 0
If IsNull(strKeyWo rds) Then Exit Function

Do
intPos = InStr(1, strKeyWords, ",")
If intPos = 0 Then
CountKeywords = CountKeywords + 1
Else
strKeyWords = Trim(Mid(strKey Words, intPos + 1))
CountKeywords = CountKeywords + 1
End If
Loop Until intPos = 0

End Function[/CODE]
Your SQL then becomes something like[CODE=sql]SELECT [your_fields]
FROM
(tblLitCategori es INNER JOIN tblLiteratureAr ticles ON
tblLitCategorie s.Abbreviation = tblLiteratureAr ticles.Abbrevia tion)
INNER JOIN tblArticleKeywo rd ON
tblLiteratureAr ticles.ArticleI D = tblArticleKeywo rd.[Article ID]
WHERE
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeywo rd.Keyword)=Tru e
AND tblLitCategorie s.Selected=True
GROUP BY [your_fields]
HAVING (((Count(tblArt icleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));[/CODE]
Substitute [your_fields] with a list of the fields you want to see, make sure you duplicate that list in the GROUP BY clause, otherwise you will see error messages about fields not being included as part of an aggregate function.

Hopefully you should be really close now.

Thanks! It works fine when searching for just one keyword, but nothing appears when searching for more than one. When I view the query in datasheet view, nothing appears at all, not even an empty row. However, I do feel like I'm much closer to solving this problem. I will keep working on it!

Thanks again for your help!

**FishVal** · Jul 31 '07, 03:44 PM

Hi, mforema.

Try the following code/SQL.

Code:

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='" & strKeyWord & "'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

[code=sql]
SELECT tblLiteratureAr ticles.*
FROM tblLiteratureAr ticles
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureAr ticles]![ArticleID]);
[/code]

Actually it is not a very good solution bkz it invokes multiple times DLookUp function which is rather slow. On large number of records performance supposed to be low, but at least it works.

**mforema** · Jul 31 '07, 05:15 PM

Originally posted by FishVal

Hi, mforema.

Try the following code/SQL.

Code:

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='" & strKeyWord & "'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

[code=sql]
SELECT tblLiteratureAr ticles.*
FROM tblLiteratureAr ticles
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureAr ticles]![ArticleID]);
[/code]

Actually it is not a very good solution bkz it invokes multiple times DLookUp function which is rather slow. On large number of records performance supposed to be low, but at least it works.

It's not working. It's running into the same problems as before. It only works with one keyword. When it does show results, it shows multiple results, because tblArticleKeywo rd consists of the composite key where each ArticleID is joined with multiple keywords, like
ArticleID Keyword
Article 4 - distillation
Article 4 - column
Article 5 - reflux
Article 5 - distillation

Thanks for your help, though!

**FishVal** · Jul 31 '07, 05:48 PM

Originally posted by mforema

It's not working. It's running into the same problems as before. It only works with one keyword. When it does show results, it shows multiple results, because tblArticleKeywo rd consists of the composite key where each ArticleID is joined with multiple keywords, like
ArticleID Keyword
Article 4 - distillation
Article 4 - column
Article 5 - reflux
Article 5 - distillation

Thanks for your help, though!

I've tested this code on a sample db with the table structure you've posted in msg#1. The names are slightly different but certainly this doesn't make difference in the solution logic. The query does retrieve records from tblLiteratureAr ticles having records in tblArticleKeywo rds for all keywords in search string.

tblArticles
keyArticleID(Lo ng,Autonumber,P K); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4

tblKeyWords
keyKeyWord(Text ,PK)
Column
Distillation
Plate
Reflux

tblArticleKeywo rds (both fields are members of composite PK)
keyArticleID(FK (tblArticles); keyKeyWord(FK(t blKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation

VBA

Code:

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("keyArticleID", "tblArticleKeyWords", _
            "keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Query
[code=sql]
SELECT tblArticles.*
FROM tblArticles
WHERE KeyWordsInStr(" Column,Distilla tion",[tblArticles]![keyArticleID]);
[/code]

returns

keyArticleID txtTitle
1 Article1
3 Article3
4 Article4

Is this what do you want?

**mforema** · Jul 31 '07, 06:15 PM

Originally posted by FishVal

I've tested this code on a sample db with the table structure you've posted in msg#1. The names are slightly different but certainly this doesn't make difference in the solution logic. The query does retrieve records from tblLiteratureAr ticles having records in tblArticleKeywo rds for all keywords in search string.

tblArticles
keyArticleID(Lo ng,Autonumber,P K); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4

tblKeyWords
keyKeyWord(Text ,PK)
Column
Distillation
Plate
Reflux

tblArticleKeywo rds (both fields are members of composite PK)
keyArticleID(FK (tblArticles); keyKeyWord(FK(t blKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation

VBA

Code:

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("keyArticleID", "tblArticleKeyWords", _
            "keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
            Exit Function
        End If
    Loop Until intPos = 0
    
    KeyWordsInStr = True
     
End Function

Query
[code=sql]
SELECT tblArticles.*
FROM tblArticles
WHERE KeyWordsInStr(" Column,Distilla tion",[tblArticles]![keyArticleID]);
[/code]

returns

keyArticleID txtTitle
1 Article1
3 Article3
4 Article4

Is this what do you want?

I feel like such an idiot...Yes, the code and the SQL work fine. I didn't paste the SQL exactly. Also, I've been trying to test the code with "distillati on, column", when "column" is not even a keyword - "columns" with an 's' is a keyword. Some of my other search forms use a code that searches for partial matches - like "dist" will bring up "distillati on" and "distillate ." I'm used to typing in partial words to test my code. Thanks so much! Yes, the code works! I'd like users to be able to type in partial words, though. Is there a way to use 'Like' and wildcards to do that with the code that you wrote?

Again, thanks for your help, and I am sorry for the confusion.

**FishVal** · Aug 1 '07, 09:08 AM

Originally posted by mforema

I feel like such an idiot...Yes, the code and the SQL work fine. I didn't paste the SQL exactly. Also, I've been trying to test the code with "distillati on, column", when "column" is not even a keyword - "columns" with an 's' is a keyword. Some of my other search forms use a code that searches for partial matches - like "dist" will bring up "distillati on" and "distillate ." I'm used to typing in partial words to test my code. Thanks so much! Yes, the code works! I'd like users to be able to type in partial words, though. Is there a way to use 'Like' and wildcards to do that with the code that you wrote?

Again, thanks for your help, and I am sorry for the confusion.

Ok. You can easily modify code to be able to use partial keywords.
Here is modified function code.

Code:

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

**mforema** · Aug 1 '07, 02:23 PM

Originally posted by FishVal

Ok. You can easily modify code to be able to use partial keywords.
Here is modified function code.

Code:

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

Awesome! It works great!
Thanks again for your help!

**FishVal** · Aug 1 '07, 02:29 PM

Originally posted by mforema

Awesome! It works great!
Thanks again for your help!

Glad it was helpful.

Help with Query and Subform

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment