Help with Query and Subform

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));

Jul 26 '07 #2

2,653

Expert 2GB

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

Expand|Select|Wrap|Line Numbers

 
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;

Jul 26 '07 #3

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

Expand|Select|Wrap|Line Numbers

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!

Jul 27 '07 #4

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.

Expand|Select|Wrap|Line Numbers

 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

Jul 27 '07 #5

Expert

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.

Expand|Select|Wrap|Line Numbers

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?

Jul 27 '07 #6

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!

Jul 27 '07 #7

2,653

Expert 2GB

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.

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

 
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

Jul 27 '07 #8

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

Expand|Select|Wrap|Line Numbers

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!

Jul 27 '07 #9

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.

Expand|Select|Wrap|Line Numbers

 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!

Jul 27 '07 #10

Okay, I think I got it fixed.

Expand|Select|Wrap|Line Numbers

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!

Jul 27 '07 #11

Expert

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.

Expand|Select|Wrap|Line Numbers

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

Loop Until intPos = 0

Will set the comparison to True for ANY keyword match

Expand|Select|Wrap|Line Numbers

 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.

Jul 30 '07 #12

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.

Expand|Select|Wrap|Line Numbers

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

Loop Until intPos = 0

Will set the comparison to True for ANY keyword match

Expand|Select|Wrap|Line Numbers

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!

Jul 30 '07 #13

Expert

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?

Jul 30 '07 #14

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

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

Jul 30 '07 #15

Expert

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.

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

 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.

Jul 31 '07 #16

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.

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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!

Jul 31 '07 #17

2,653

Expert 2GB

Hi, mforema.

Try the following code/SQL.

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
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.

Jul 31 '07 #18

Hi, mforema.

Try the following code/SQL.

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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!

Jul 31 '07 #19

2,653

Expert 2GB

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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
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?

Jul 31 '07 #20

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

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

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.

Jul 31 '07 #21

2,653

Expert 2GB

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.

Expand|Select|Wrap|Line Numbers

 
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

Aug 1 '07 #22

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

Expand|Select|Wrap|Line Numbers

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!

Aug 1 '07 #23