Help with Query and Subform

[mforema]

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles:

Abbreviation(FK)
ArticleID(PK)
Author
Title
etc...

tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategories (one-to-many relationship with tblLiteratureArticles)

MajorCategory
Abbreviation(PK)
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

[mforema]

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles:

Abbreviation(FK)
ArticleID(PK)
Author
Title
etc...

tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategories (one-to-many relationship with tblLiteratureArticles)

MajorCategory
Abbreviation(PK)
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));

[FishVal]

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));

Hi.

I'd like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

SELECT 
tblLiteratureArticles.Abbreviation, 
tblLiteratureArticles.ArticleID, 
tblLiteratureArticles.PrincipalAuthor, 
tblLiteratureArticles.TITLE, 
tblLiteratureArticles.Journal, 
tblLiteratureArticles.Volume, 
tblLiteratureArticles.Number, 
tblLiteratureArticles.PAGES, 
tblLiteratureArticles.DATE, 
tblLiteratureArticles.OtherAuthors, 
tblLiteratureArticles.Link, 
tblArticleKeyword.Keyword
FROM 
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE 
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True; 

[mforema]

Hi.

I'd like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

SELECT 
tblLiteratureArticles.Abbreviation, 
tblLiteratureArticles.ArticleID, 
tblLiteratureArticles.PrincipalAuthor, 
tblLiteratureArticles.TITLE, 
tblLiteratureArticles.Journal, 
tblLiteratureArticles.Volume, 
tblLiteratureArticles.Number, 
tblLiteratureArticles.PAGES, 
tblLiteratureArticles.DATE, 
tblLiteratureArticles.OtherAuthors, 
tblLiteratureArticles.Link, 
tblArticleKeyword.Keyword
FROM 
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE 
KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True; 

Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeywords), but I got no results in the query. Was I supposed to change something in the function?

Thanks for your help!

[mforema]

Thanks! I pasted the SQL exactly, and I also pasted the function into a module. But it's not working. I tried to type in two keywords into the textbox(txtKeywords), but I got no results in the query. Was I supposed to change something in the function?

Thanks for your help!

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
Else
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function

[kepston]

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
Else
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function

I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?

[mforema]

I have been doing something similar.
The code (quoted here) worked for me.
Are you separating your search keywords with commas?
Have you tried to debug the code yourself, using the 'step through' facility within VBA?

Yes, I separate the keywords by commas.
I have tried to debug the code myself, but I have not used the 'step through' facility; I've never heard of it. If the code worked for you, I am even more confused. I copy-and-pasted the SQL and the Function code, so I shouldn't have any syntax errors.
Do you think the problem could be in the way my tables are set-up? I have the keywords in a join table, so that each article can be assigned mutliple keywords using a composite key. So, article 1 could be paired with more than one keyword. Is this code designed to take that into account, or does it even matter?

Thanks for your help!

[FishVal]

The function works with one keyword, but it does not work with 2 or more. I believe the code for the function should be debugged, but I can't see what's wrong with it. I know that the Else statement within the Do Loop should be storing the keywords, right? I can't see why the function isn't returning more than one keyword. Of course, I am a newbie code writer; the logic is screwed up somewhere, but I can't see it.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
Else
strKeyWord = Trim(Left(strKeyWords, intPos - 1))
strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
End If
If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
 
KeyWordsInStr = True
End Function

Hi and apologies. Certainly this code will not work anyway. Here is working one.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
    Loop Until intPos = 0
 
End Function
 

[mforema]

Hi and apologies. Certainly this code will not work anyway. Here is working one.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
        If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
    Loop Until intPos = 0
 
End Function
 

Awesome! It worked! Well, kinda...there are two problems:

1. It didn't narrow the search. If I type in two keywords like this, "distillation, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.

2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'

Thanks for your help! I will keep trying!

[mforema]

Awesome! It worked! Well, kinda...there are two problems:

1. It didn't narrow the search. If I type in two keywords like this, "distillation, column", the query will return articles that have either one keyword or the other. I need it to only show the articles that have both keywords.

2. When the results show up in datasheet view, articles show up multiple times. For instance, article 4 has distillation and column as keywords. If I type in 'distillation, column' into the search box, article 4 is displayed twice - one time for 'distillation' and one time for 'column.'

Thanks for your help! I will keep trying!

Okay, I think I got it fixed.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
    Loop Until intPos = 0
    If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
 
End Function

I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillation, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.

I will keep testing it, but I think it's right!

Thanks again for your help!

[mforema]

Okay, I think I got it fixed.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
        Else
            strKeyWord = Trim(Left(strKeyWords, intPos - 1))
            strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
        End If
    Loop Until intPos = 0
    If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
 
End Function

I only need to show the article if the last keyword typed into the search box is a match. For instance, if I again use "distillation, column" in the search box, each article will be searched for the first keyword and then the second keyword. Well, if the second keyword is a match then the article will show, BUT if the second keyword is not a match then the article will not show. I did this by putting the If Then statement outside the Do Loop.

I will keep testing it, but I think it's right!

Thanks again for your help!

NOOOO! It doesn't work! I tried switching the keywords' order from "distillation, column" to "column, distillation." Well, some articles only have "distillation" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(

But, I will keep trying!

[kepston]

NOOOO! It doesn't work! I tried switching the keywords' order from "distillation, column" to "column, distillation." Well, some articles only have "distillation" as a keyword. So, since the code only chooses based on the last keyword, the query shows only the articles for the last keyword. I don't know why I thought it would work. I guess I jumped the gun :(

But, I will keep trying!

To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)

Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order.

If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
Loop Until intPos = 0

Will set the comparison to True for ANY keyword match

If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
KeyWordsInStr = True

Will set the comparison to False for the first keyword that IS NOT found.

If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillation Column Retort Fraction"
Article = 4

Not:
Keyword = "Distillation", Article = 4
Keyword = "Column", Article = 4
etc

Hopefully that clarifies a few points.

[mforema]

To step through your code you need to set a breakpoint - press F9 on a line of code (or Menu bar, Debug, Toggle Breakpoint. Or click in the vertical grey bar to the left of the code line), a brown spot will appear in the vertical grey bar and the line of code will be highlighted brown.
When the code is executed, it will run until the breakpoint is encountered.
From that point you can step through line by line, using F8 (other options available from Debug menu). You can see the values of your variables by hovering over their name with your mouse pointer or by right-clicking and selecting Add Watch.
This way you can see exactly what is happening and why.
Often you will want to stop the code - Menu bar, Run, Reset (or Reset button - looks like a VCR stop button, i.e. solid square)
Other times you will want the code to run without prompting - Menu bar, Run, Continue (F5), (or Continue button - VCR play)

Back to your code:
By placing the test for keywords outside of the loop, you will only be testing the current, i.e. last keyword, effectively ignoring all the others.
Testing inside the loop compares every keyword in order.

If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
Loop Until intPos = 0

Will set the comparison to True for ANY keyword match

If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
Loop Until intPos = 0
KeyWordsInStr = True

Will set the comparison to False for the first keyword that IS NOT found.

If you are using the function in it's intended manor, then it will be called for every record in your table.
Note that the field containing your keywords must contain all of the keywords, and not a separate record for each keyword.
E.g.
Keywords = "Distillation Column Retort Fraction"
Article = 4

Not:
Keyword = "Distillation", Article = 4
Keyword = "Column", Article = 4
etc

Hopefully that clarifies a few points.

So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.

Thanks for your help!

[kepston]

So, I shouldn't have a join table with a composite key as described in my first post of this discussion? I should have all keywords in the same field for each article/record? I tried that at first, but I was posting on another discussion, and someone told me that my tables weren't normalized. He/She said that I should use a composite key in a join table. I understand the code now, but I am confused about my tables.

Thanks for your help!

Can you post a link to the other discussion for me please?

[mforema]

Can you post a link to the other discussion for me please?

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

[kepston]

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.

Public Function KeyWordsInStr(ByVal 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(strKeyWords)
      Else
        strKeyWord = Trim(Left(strKeyWords, intPos - 1))
        strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
      End If
      If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
    Loop Until intPos = 0
 
End Function

Public Function CountKeywords(ByVal strKeyWords As String) As Integer
    Dim intPos As Integer
 
    CountKeywords = 0
    If IsNull(strKeyWords) Then Exit Function
 
    Do
      intPos = InStr(1, strKeyWords, ",")
      If intPos = 0 Then
          CountKeywords = CountKeywords + 1
      Else
          strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
          CountKeywords = CountKeywords + 1
      End If
    Loop Until intPos = 0
 
End Function

Your SQL then becomes something like

SELECT [your_fields]
FROM
      (tblLitCategories INNER JOIN tblLiteratureArticles ON
         tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
      INNER JOIN tblArticleKeyword ON 
         tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
      KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True 
  AND tblLitCategories.Selected=True
GROUP BY [your_fields]
HAVING (((Count(tblArticleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));

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]

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.

Public Function CountKeywords(ByVal strKeyWords As String) As Integer
    Dim intPos As Integer
 
    CountKeywords = 0
    If IsNull(strKeyWords) Then Exit Function
 
    Do
      intPos = InStr(1, strKeyWords, ",")
      If intPos = 0 Then
          CountKeywords = CountKeywords + 1
      Else
          strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
          CountKeywords = CountKeywords + 1
      End If
    Loop Until intPos = 0
 
End Function

Your SQL then becomes something like

SELECT [your_fields]
FROM
      (tblLitCategories INNER JOIN tblLiteratureArticles ON
         tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
      INNER JOIN tblArticleKeyword ON 
         tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
      KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True 
  AND tblLitCategories.Selected=True
GROUP BY [your_fields]
HAVING (((Count(tblArticleKeyword.[Article ID])) = CountKeywords([Forms]![Form1]![txtKeywords])));

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]

Hi, mforema.

Try the following code/SQL.

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
 

SELECT tblLiteratureArticles.*
FROM tblLiteratureArticles
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
 

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]

Hi, mforema.

Try the following code/SQL.

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
 

SELECT tblLiteratureArticles.*
FROM tblLiteratureArticles
WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
 

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 tblArticleKeyword 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]

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 tblArticleKeyword 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 tblLiteratureArticles having records in tblArticleKeywords for all keywords in search string.

tblArticles
keyArticleID(Long,Autonumber,PK); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4

tblKeyWords
keyKeyWord(Text,PK)
Column
Distillation
Plate
Reflux

tblArticleKeywords (both fields are members of composite PK)
keyArticleID(FK(tblArticles); keyKeyWord(FK(tblKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation

VBA

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

SELECT tblArticles.*
FROM tblArticles
WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
 

returns

keyArticleID txtTitle
1 Article1
3 Article3
4 Article4

Is this what do you want?

[mforema]

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 tblLiteratureArticles having records in tblArticleKeywords for all keywords in search string.

tblArticles
keyArticleID(Long,Autonumber,PK); txtTitle(Text)
1 Article1
2 Article2
3 Article3
4 Article4

tblKeyWords
keyKeyWord(Text,PK)
Column
Distillation
Plate
Reflux

tblArticleKeywords (both fields are members of composite PK)
keyArticleID(FK(tblArticles); keyKeyWord(FK(tblKeyWords))
Article1 Column
Article1 Distillation
Article2 Distillation
Article2 Reflux
Article3 Column
Article3 Distillation
Article3 Reflux
Article4 Column
Article4 Distillation

VBA

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

SELECT tblArticles.*
FROM tblArticles
WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
 

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 "distillation, 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 "distillation" 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]

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 "distillation, 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 "distillation" 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.

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]

Ok. You can easily modify code to be able to use partial keywords.
Here is modified function 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]

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

Glad it was helpful.