469,641 Members | 1,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,641 developers. It's quick & easy.

Help with Query and Subform

72
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!
Jul 25 '07 #1
23 2103
mforema
72
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
FishVal
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
  1. SELECT 
  2. tblLiteratureArticles.Abbreviation, 
  3. tblLiteratureArticles.ArticleID, 
  4. tblLiteratureArticles.PrincipalAuthor, 
  5. tblLiteratureArticles.TITLE, 
  6. tblLiteratureArticles.Journal, 
  7. tblLiteratureArticles.Volume, 
  8. tblLiteratureArticles.Number, 
  9. tblLiteratureArticles.PAGES, 
  10. tblLiteratureArticles.DATE, 
  11. tblLiteratureArticles.OtherAuthors, 
  12. tblLiteratureArticles.Link, 
  13. tblArticleKeyword.Keyword
  14. FROM 
  15. (tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  16. WHERE 
  17. KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True AND tblLitCategories.Selected=True; 
Jul 26 '07 #3
mforema
72
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
  1. SELECT 
  2. tblLiteratureArticles.Abbreviation, 
  3. tblLiteratureArticles.ArticleID, 
  4. tblLiteratureArticles.PrincipalAuthor, 
  5. tblLiteratureArticles.TITLE, 
  6. tblLiteratureArticles.Journal, 
  7. tblLiteratureArticles.Volume, 
  8. tblLiteratureArticles.Number, 
  9. tblLiteratureArticles.PAGES, 
  10. tblLiteratureArticles.DATE, 
  11. tblLiteratureArticles.OtherAuthors, 
  12. tblLiteratureArticles.Link, 
  13. tblArticleKeyword.Keyword
  14. FROM 
  15. (tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  16. WHERE 
  17. 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
mforema
72
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
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) 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 Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
Jul 27 '07 #5
kepston
97 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
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) 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 Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. 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
mforema
72
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
FishVal
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
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2. ByVal varField As Variant) 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 Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  17. Loop Until intPos = 0
  18.  
  19. KeyWordsInStr = True
  20. End Function
Hi and apologies. Certainly this code will not work anyway. Here is working one.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) 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 Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  17.     Loop Until intPos = 0
  18.  
  19. End Function
  20.  
Jul 27 '07 #8
mforema
72
Hi and apologies. Certainly this code will not work anyway. Here is working one.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) 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 Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  17.     Loop Until intPos = 0
  18.  
  19. End Function
  20.  
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
mforema
72
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
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) 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.     Loop Until intPos = 0
  17.     If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  18.  
  19. 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
mforema
72
Okay, I think I got it fixed.
Expand|Select|Wrap|Line Numbers
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.                               ByVal varField As Variant) 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.     Loop Until intPos = 0
  17.     If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  18.  
  19. 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
kepston
97 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
  1. If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  2. Loop Until intPos = 0
Will set the comparison to True for ANY keyword match

Expand|Select|Wrap|Line Numbers
  1. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  2. Loop Until intPos = 0
  3. 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
mforema
72
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
  1. If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  2. Loop Until intPos = 0
Will set the comparison to True for ANY keyword match

Expand|Select|Wrap|Line Numbers
  1. If Nz(InStr(1, varField, strKeyWord)) = 0 Then Exit Function
  2. Loop Until intPos = 0
  3. 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
kepston
97 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
mforema
72
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
kepston
97 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
  1. Public Function KeyWordsInStr(ByVal strKeyWords As String, _
  2.       ByVal varField As Variant) As Boolean
  3.  
  4.     Dim intPos As Integer
  5.     Dim strKeyWord As String
  6.  
  7.     KeyWordsInStr = False
  8.  
  9.     Do
  10.       intPos = InStr(1, strKeyWords, ",")
  11.       If intPos = 0 Then
  12.           strKeyWord = Trim(strKeyWords)
  13.       Else
  14.         strKeyWord = Trim(Left(strKeyWords, intPos - 1))
  15.         strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  16.       End If
  17.       If Nz(InStr(1, varField, strKeyWord)) <> 0 Then KeyWordsInStr = True
  18.     Loop Until intPos = 0
  19.  
  20. End Function
Expand|Select|Wrap|Line Numbers
  1. Public Function CountKeywords(ByVal strKeyWords As String) As Integer
  2.     Dim intPos As Integer
  3.  
  4.     CountKeywords = 0
  5.     If IsNull(strKeyWords) Then Exit Function
  6.  
  7.     Do
  8.       intPos = InStr(1, strKeyWords, ",")
  9.       If intPos = 0 Then
  10.           CountKeywords = CountKeywords + 1
  11.       Else
  12.           strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  13.           CountKeywords = CountKeywords + 1
  14.       End If
  15.     Loop Until intPos = 0
  16.  
  17. End Function
Your SQL then becomes something like
Expand|Select|Wrap|Line Numbers
  1. SELECT [your_fields]
  2. FROM
  3.       (tblLitCategories INNER JOIN tblLiteratureArticles ON
  4.          tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
  5.       INNER JOIN tblArticleKeyword ON 
  6.          tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  7. WHERE
  8.       KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True 
  9.   AND tblLitCategories.Selected=True
  10. GROUP BY [your_fields]
  11. 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
mforema
72
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
  1. Public Function CountKeywords(ByVal strKeyWords As String) As Integer
  2.     Dim intPos As Integer
  3.  
  4.     CountKeywords = 0
  5.     If IsNull(strKeyWords) Then Exit Function
  6.  
  7.     Do
  8.       intPos = InStr(1, strKeyWords, ",")
  9.       If intPos = 0 Then
  10.           CountKeywords = CountKeywords + 1
  11.       Else
  12.           strKeyWords = Trim(Mid(strKeyWords, intPos + 1))
  13.           CountKeywords = CountKeywords + 1
  14.       End If
  15.     Loop Until intPos = 0
  16.  
  17. End Function
Your SQL then becomes something like
Expand|Select|Wrap|Line Numbers
  1. SELECT [your_fields]
  2. FROM
  3.       (tblLitCategories INNER JOIN tblLiteratureArticles ON
  4.          tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation)
  5.       INNER JOIN tblArticleKeyword ON 
  6.          tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
  7. WHERE
  8.       KeyWordsInStr([Forms]![Form1]![txtKeywords], tblArticleKeyword.Keyword)=True 
  9.   AND tblLitCategories.Selected=True
  10. GROUP BY [your_fields]
  11. 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
FishVal
2,653 Expert 2GB
Hi, mforema.

Try the following code/SQL.

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='" & strKeyWord & "'")) Then
  18.             Exit Function
  19.         End If
  20.     Loop Until intPos = 0
  21.  
  22.     KeyWordsInStr = True
  23.  
  24. End Function
  25.  
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLiteratureArticles.*
  2. FROM tblLiteratureArticles
  3. WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
  4.  
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
mforema
72
Hi, mforema.

Try the following code/SQL.

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='" & strKeyWord & "'")) Then
  18.             Exit Function
  19.         End If
  20.     Loop Until intPos = 0
  21.  
  22.     KeyWordsInStr = True
  23.  
  24. End Function
  25.  
Expand|Select|Wrap|Line Numbers
  1. SELECT tblLiteratureArticles.*
  2. FROM tblLiteratureArticles
  3. WHERE KeyWordsInStr([Forms]![Form1]![txtKeywords],[tblLiteratureArticles]![ArticleID]);
  4.  
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
FishVal
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
  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("keyArticleID", "tblArticleKeyWords", _
  17.             "keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
  18.             Exit Function
  19.         End If
  20.     Loop Until intPos = 0
  21.  
  22.     KeyWordsInStr = True
  23.  
  24. End Function
  25.  
  26.  
Query
Expand|Select|Wrap|Line Numbers
  1. SELECT tblArticles.*
  2. FROM tblArticles
  3. WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
  4.  
returns

keyArticleID txtTitle
1 Article1
3 Article3
4 Article4

Is this what do you want?
Jul 31 '07 #20
mforema
72
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
  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("keyArticleID", "tblArticleKeyWords", _
  17.             "keyArticleID=" & lngArticleID & " AND keyKeyWord='" & strKeyWord & "'")) Then
  18.             Exit Function
  19.         End If
  20.     Loop Until intPos = 0
  21.  
  22.     KeyWordsInStr = True
  23.  
  24. End Function
  25.  
  26.  
Query
Expand|Select|Wrap|Line Numbers
  1. SELECT tblArticles.*
  2. FROM tblArticles
  3. WHERE KeyWordsInStr("Column,Distillation",[tblArticles]![keyArticleID]);
  4.  
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
FishVal
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
  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
  26.  
Aug 1 '07 #22
mforema
72
Ok. You can easily modify code to be able to use partial keywords.
Here is modified function code.

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
  26.  
Awesome! It works great!
Thanks again for your help!
Aug 1 '07 #23
FishVal
2,653 Expert 2GB
Awesome! It works great!
Thanks again for your help!
Glad it was helpful.
Aug 1 '07 #24

Post your reply

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

Similar topics

1 post views Thread by Kunal | last post: by
2 posts views Thread by John | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.